How do I parse nested JSON with JQ into CSV-aggregated output?

Question

I have a question that is an extension/followup to a previous question I've asked: How do I concatenate dummy values in JQ based on field value, and then CSV-aggregate these concatenations?

In my bash script, when I run the following jq against my curl result:
curl -u someKey:someSecret someURL 2>/dev/null | jq -r '.schema' | jq -r -c '.fields'

I get back a JSON array as follows:

[
    {"name":"id", "type":"int"},
    {
        "name": "agents",
        "type": {
            "type": "array",
            "items": {
                "name": "carSalesAgents",
                "type": "record"
                "fields": [
                    {
                        "name": "agentName",
                        "type": ["string", "null"],
                        "default": null
                    },
                    {
                        "name": "agentEmail",
                        "type": ["string", "null"],
                        "default": null
                    },
                    {
                        "name": "agentPhones",
                        "type": {
                            "type": "array",
                            "items": {
                                "name": "SalesAgentPhone",
                                "type": "record"
                                "fields": [
                                    {
                                    "name": "phoneNumber",
                                    "type": "string"
                                    }
                                ]
                            }
                        },
                        "default": []
                    }
                ]
            }
        },
        "default": []
    },
    {"name":"description","type":"string"}
]

Note: line breaks and indentation added here for ease of reading. This is all in reality a single blob of text.

My goal is to do a call with jq applied to return the following, given the example above (again lines and spaces added for readability, but only need to return valid JSON blob):

{
"id":1234567890,
"agents": [
    {
        "agentName": "xxxxxxxxxx",
        "agentEmail": "xxxxxxxxxx",
        "agentPhones": [
            {
                "phoneNumber": "xxxxxxxxxx"
            },
            {
                "phoneNumber": "xxxxxxxxxx"
            },
            {
                "phoneNumber": "xxxxxxxxxx"
            }
        ]
    },
    {
        "agentName": "xxxxxxxxxx",
        "agentEmail": "xxxxxxxxxx",
        "agentPhones": [
            {
                "phoneNumber": "xxxxxxxxxx"
            },
            {
                "phoneNumber": "xxxxxxxxxx"
            },
            {
                "phoneNumber": "xxxxxxxxxx"
            }
        ]
    }
],
"description":"xxxxxxxxxx"
}

To summarise, I am trying to automatically generate templated values that match the "schema" JSON shown above.

So just to clarify, the values for "name" (including their surrounding double-quotes) are concatenated with either:

:1234567890 ...when the "type" for that object is "int"
":xxxxxxxxxx" ...when the "type" for that object is "string"
...and when type is "array" or "record" the appropriate enclosures are added {} or [] with the nested content inside.
if its an array of records, generate TWO records for the output

The approach I have started down to cater for parsing nested content like this is to have a series of if-then-else's for every combination of each possible jq type.

But this is fast becoming very hard to manage and painful. From my initial scratch efforts...

echo '[{"name":"id","type":"int"},{"name":"test_string","type":"string"},{"name":"string3ish","type":["string","null"],"default":null}]' | jq -c 'map({(.name): (if .type == "int" then 1234567890 else (if .type == "string" then "xxxxxxxxxx" else (if .type|type == "array" then "xxARRAYxx" else "xxUNKNOWNxx" end) end) end)})|add'

I was wondering if anyone knew of a smarter way to do this in bash/shell with JQ.

PS: I have found alternate solutions for such parsing using Java and Python modules, but JQ is preferable for a unique case of limitations around portability. :)

Thanks!

Smaller samples would be way easier to read and understand for us — oguz ismail
– oguz ismail, Commented Jun 3, 2020 at 11:55
@oguzismail yeah I cut this down from a much much larger sample. I figured this was as small as I could make it and still (clearly) demonstrate the amount of nesting I'm trying to parse. — Stan Bridge
– Stan Bridge, Commented Jun 3, 2020 at 22:25
@alecxs You mean using a for loop + case in bash and jq over each iteration? Like this: starkandwayne.com/blog/bash-for-loop-over-json-array-using-jq ? (research continued after this post :) ) ...or is there a way to loop in jq (couldn't find one) and then make better use of if-then-elif-else thanI have? — Stan Bridge
– Stan Bridge, Commented Jun 3, 2020 at 22:30
Also, note that answers to your question can't be tested right now because the given "JSON" isn't valid. Passing it to even just jq . yields parse error: Expected separator between values at line 10, column 24 — Charles Duffy
– Charles Duffy, Commented Jun 3, 2020 at 23:05

Charles Duffy · Accepted Answer · 2020-06-03 23:43:51Z

1

jq supports functions. Those functions can recurse.

#!/usr/bin/env jq -f

# Ignore all but the first type, in the case of "type": ["string", "null"]
def takeFirstTypeFromArray:
  if (.type | type) == "array" then
    .type = .type[0]
  else
    .
  end;

def sampleData:
  takeFirstTypeFromArray |
  if .type == "int" then
    1234567890
  elif .type == "string" then
    "xxxxxxxxxx"
  elif .type == "array" then   # generate two entries for any test array
    [(.items | sampleData), (.items | sampleData)]
  elif .type == "record" then
    (.fields | map({(.name): sampleData}) | add)
  elif (.type | type) == "array" then
    (.type[] | sampleData)
  elif (.type | type) == "object" then
    (.type | sampleData)
  else
    ["UNKNOWN", .]
  end;

map({(.name): sampleData}) | add

answered Jun 3, 2020 at 23:43

Charles Duffy

299k43 gold badges442 silver badges498 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

peak Over a year ago

VND! One small efficiency enhancement: [(.items | sampleData), (.items | sampleData)] => [(.items | sampleData) | (.,.)]

Collectives™ on Stack Overflow

How do I parse nested JSON with JQ into CSV-aggregated output?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related