Flattening nested JSON objects with jq#

I wanted to take a nested set of JSON objects and import them into a SQLite database using sqlite-utils insert - but I wanted to “flatten” some of the nested rows.

Example data:

1
{
2
  "status": "success",
3
  "data": {
4
    "generated": "2021-02-18T20:14:02.288Z",
5
    "sites": [
6
      {
7
        "id": "full_data",
8
        "name": "Moscone Center South (full data)",
9
        "active": true,
10
        "location": {
11
          "address": "747 Howard St, San Francisco, CA 94103",
12
          "url": "https://www.google.com/maps/place/Moscone+Center+South,+747+Howard+St,+San+Francisco,+CA+94103",
13
          "lng": -122.401253,
14
          "lat": 37.78392
15
        },
16
        "info": {
17
          "url": "https://sf.gov/location/moscone-center-south-covid-19-vaccine-site"
18
        },
19
        "booking": {
20
          "url": "https://myturn.ca.gov",
21
          "dropins": false,
22
          "info": null
23
        },
24
        "access": {
25
          "wheelchair": true,
26
          "languages": {
27
            "en": true,
28
            "es": true,
29
            "zh": true,
30
            "fil": false,
31
            "vi": false,
32
            "ru": false
33
          },
34
          "remote_translation": {
35
            "available": false,
36
            "info": null
37
          }
38
        },
39
        "access_mode": {
40
          "walk": true,
41
          "drive": false
42
        },
43
        "open_to": {
44
          "everyone": true,
45
          "text": "Open to the public"
46
        },
47
        "appointments": {
48
          "available": true,
49
          "last_updated": "2021-02-18T20:14:02.288Z"
50
        },
51
        "eligibility": {
52
          "65_and_over": true,
53
          "healthcare_workers": true,
54
          "education_and_childcare": false,
55
          "agriculture_and_food": false,
56
          "emergency_services": false
57
        }
58
      }
59
    ]
60
  }
61
}

I wanted to turn this into an array of non-nested objects, like this:

1
[
2
  {
3
    "id": "full_data",
4
    "name": "Moscone Center South (full data)",
5
    "active": true,
6
    "location_address": "747 Howard St, San Francisco, CA 94103",
7
    "location_url": "https://www.google.com/maps/place/Moscone+Center+South,+747+Howard+St,+San+Francisco,+CA+94103",
8
    "location_lng": -122.401253,
9
    "location_lat": 37.78392
10
  }
11
]

Thanks to this StackOverflow answer I found the following jq fragment:

1
[leaf_paths as $path | {
2
    "key": $path | join("_"), "value": getpath($path)
3
}] | from_entries

This fragment transforms a nested JSON object into a flat one with "location_address" style keys instead.

I like trying these things out in interactive tools - https://www.jqkungfu.com/ is my current favourite, which runs the original jq in your browser compiled to WebAssembly.

I pasted in the example from above and then used this jq query to confirm that it works - the .data.sites[] | [ ... ] pattern here pulls out the ["data"]["sites"] array and applies the flatten transformation to every item within it:

1
.data.sites[] | [ [leaf_paths as $path | {"key": $path | join("_"), "value": getpath($path)}] | from_entries ]

It worked!

The final, full recipe I used to pull down the JSON, transform and flatten it and insert it into a SQLite database was this:

1
curl 'https://vaccination-site-microservice.vercel.app/api/v1/sites' | \
2
  jq .data.sites | jq '
3
    [.[] |
4
    [leaf_paths as $path | {"key": $path | join("_"), "value": getpath($path)}]
5
    | from_entries]
6
  ' | \
7
  sqlite-utils insert /tmp/sf.db sites -