Parsing json values and converting them to csv using jq

Question

I need your help for parsing below json file and converting them to csv using jq command.

  {
        "id": 15,
        "description": "package",
        "active": true,
        "name": "linux",
        "project": [
        {
            "id": 1762,
            "description": "This Red Hat Server 7 is built from the Redhat Official",
            "path": "x86_24",
            "url": "some url"
        },
        {
            "id": 1663,
            "description": "This Ubuntu 20.04 is built from the Ubuntu Official",
            "path": "x86_24",
            "url": "some url"
        },
        {
            "id": 1557,
            "description": "This Centos 7 is built from the Centos Official",
            "path": "x86_24",
            "url": "some url"
        }
    ]
    }
    {
        "id": 22,
        "description": "exe",
        "active": true,
        "name": "windows",
        "project": []
    }
    {
        "id": 34,
        "description": "brew",
        "active": true,
        "name": "mac",
        "project": []
    }

The values which I need from this json is: id, description, project.id, project.description, project.url. I tried doing with jq cmd, but at last my csv is getting messed up. Here id holds project, project has multiple ids. I need to separate them and generate my csv like below. I'm stuck up here. Any solution for this ? Thanks in Advance !

pmf · Accepted Answer · 2021-11-17 08:31:56Z

2

Is this what you are looking for:

jq -r '
  [.id, .description] + (.project[] | [.id, .description, .url])
  | @csv
'

15,"package",1762,"This Red Hat Server 7 is built from the Redhat Official","some url"
15,"package",1663,"This Ubuntu 20.04 is built from the Ubuntu Official","some url"
15,"package",1557,"This Centos 7 is built from the Centos Official","some url"
22,"exe",1332,"This Windows 7 is developed from the Windows Official","some url"
22,"exe",1563,"This Windows 11 is developed from the Windows Official","some url"

Demo

You can also adopt @ikegami's solution to a (technically) very similar problem:

jq -r '
  .project[] as $p
  | [.id, .description, $p.id, $p.description, $p.url]
  | @csv
'

Demo

edited Nov 17, 2021 at 8:31

answered Nov 17, 2021 at 8:08

pmf

38.3k3 gold badges31 silver badges41 bronze badges

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

9 Comments

pmf Over a year ago

If you want to include a header row, simply prepend it as an array of strings and a final comma like so ["id", "description", "project_id", "project_description", "project_url"], .

pmf Over a year ago

For a header row in the alternative solution, another parenthesis is required: [...], (...) | @csv.

gamer_rulez Over a year ago

Hey.. this is what I'm looking for.. Thanks a lot ! But, I dont want that id (15,22) to get repeated.. Anyway to remove them alone using jq ? (not project id).. Let me know if I'm not clear

gamer_rulez Over a year ago

I need your help on this scenario.. sometimes my project will be null "project": []. At that time, my id and description should get printed. cmd which you shared is not working in this scenario.. { "id": 22, "description": "exe", "active": true, "name": "windows", "project": [] }

pmf Over a year ago

Actually, CSV as a data construct doesn't work that way. But a custom formatting is no problem for jq. Can you post your desired output in text format so that I can see what exactly should happen with your sample data? (Maybe you want empty fields to maintain the overall number of fields, which is 5, or maybe you want them missing entirely, giving you only the number of fields that have changed, or ... something else).

|

Collectives™ on Stack Overflow

Parsing json values and converting them to csv using jq

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related