Question 108758 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

jq query to convert nested json to csv

I am new to jq and try to process a nested json file and convert it to csv format. The sample nested json file is composed of three levels as below:

{
level1-label: {
    level2-text1 : {
        level3-label1: value1,
        level3-label2: value2,
        level3-label3: value3
    },
    level2-text2 : {
        level3-label1: value4,
        level3-label2: value5,
        level3-label3: value6
    }
}
}

Desired output

level2, level3-label1, level3-label2, level3-lable3

level2-text1, value1, value2, value3
level2-text2, value4, value5, value6

Having research for many hours, it seems the function "to_entries" may be the proper solution. I could finally come up with two queries to process level2 and level3 data as an array but could not go further.

Query with result of arrays just containing level2 keys

jq -r ".level1-label | to_entries | map([.key])" input.json

Query with result of arrays just containing level3 keys and values

jq -r ".level1-label | to_entries | map(.value)" input.json

Please advice the direction to combine the above queries or other solution.

Tags:

2

Answers


  1. 0

    Use | (Pipe) operator to pass the output of one command as an input to another command. You need this to combine both the queries:

    jq -r '.["level1-label"] | to_entries[] | [.key] + (.value | to_entries[] | [.key, .value]) | @csv' input.json

    should work on JSON structure:

    {
    "level1-label": {
        "level2-text1": {
            "level3-label1": "value1",
            "level3-label2": "value2",
            "level3-label3": "value3"
        },
        "level2-text2": {
            "level3-label1": "value4",
            "level3-label2": "value5",
            "level3-label3": "value6"
        }
    }
}
    Login or Signup to reply.
  2. 0

    You can use to_entries to access the .key while iterating through the values under .value, collect everything into a new array […], and have @csv create from it the CSV formatting:

    jq -r '."level1-label" | to_entries[] | [.key, .value[]] | @csv' input.json

    "level2-text1","value1","value2","value3"
"level2-text2","value4","value5","value6"
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search