Querying JSON data with jq

Sometimes I have large JSON files that I need to extract information from. I found jq to be a nice tool to use for such tasks.

I'm using this data for the following examples:

{
    "rows": [
        {
            "id": "1",
            "name": "Foo",
            "redirectto": "0"
        },
        {
            "id": "2",
            "name": "Bar",
            "redirectto": "1"
        },
        {
            "id": "3",
            "name": "Baz",
            "redirectto": "1"
        }
    ]
}

Output an object if an attribute is larger than X​

In an array of object I wanted to print only those who had a redirectto attribute of more than 0. Unfortunately, the attribute was a string - altough its value space were only numbers (integers).

Thus I had to cast the redirectto value to int first:

$ jq '.rows[] | select((.redirectto|tonumber) > 0) | {name}' data.json
{
  "name": "Bar"
}
{
  "name": "Baz"
}

Generating SQL​

Another task I had was to update records in an SQL database with the redirect values. I figured out that I could simply let jq create SQL queries that I could use.

$ jq '.rows[] | select((.redirectto|tonumber) > 0) | ["UPDATE pages SET target=", .redirectto, " WHERE oldid=", .id] | add' data.json\
  | sed -e 's/^"//' -e 's/"$//'
UPDATE pages SET target=1 WHERE oldid=2
UPDATE pages SET target=1 WHERE oldid=3

| add implodes the array into a single string. I manually inspected the generated SQL; there is no escaping going on here - you have to be careful.