jq - select objects and print null for missing

Question

I'm trying to generate a CSV of sort from json file, the files are as below

cat role1.json
{
  "Tags": [
    {
      "Key": "Name",
      "Value": "Role1Name"
    },
    {
      "Key": "ID",
      "Value": "Role1ID"
    },
    {
      "Key": "Manager",
      "Value": "Role1Manager"
    },
    {
      "Key": "User",
      "Value": "Role1User"
    },
    {
      "Key": "Country",
      "Value": "USA"
    }
  ]
}

cat role2.json
{
  "Tags": [
    {
      "Key": "Name",
      "Value": "Role2Name"
    },
    {
      "Key": "ID",
      "Value": "Role2ID"
    },
    {
      "Key": "City",
      "Value": "NewYork"
    },
    {
      "Key": "Creator",
      "Value": "Role2Creator"
    },
    {
      "Key": "User",
      "Value": "Role2User"
    }
  ]
}

cat role3.json
{
  "Tags": [
    {
      "Key": "Name",
      "Value": "Role3Name"
    },
    {
      "Key": "ID",
      "Value": "Role3ID"
    },
    {
      "Key": "Creator",
      "Value": "Role3Creator"
    },
    {
      "Key": "ZIP",
      "Value": 82378
    },
    {
      "Key": "Manager",
      "Value": "Role3Manager"
    },
    {
      "Key": "User",
      "Value": "Role3User"
    }
  ]
}

I want to generate lines from each of these to be later used as CSV, something like:

Role1Name, Role1ID, null, Role1Manager, Role1User
Role2Name, Role2ID, Role2Creator, null, Role2User
Role3Name, Role3ID, Role3Creator, Role3Manager, Role3User

For the header line Name, ID, Creator, Manager, User

I'm able to get all the "Value" but not able to print null for missing "Key"

$cat role1.json | jq -rc  '[.Tags[] | select(.Key == ("Name","ID","Creator","Manager","User")) | .Value]'
["Role1Name","Role1ID","Role1Manager","Role1User"]

$cat role2.json | jq -rc  '[.Tags[] | select(.Key == ("Name","ID","Creator","Manager","User")) | .Value]'
["Role2Name","Role2ID","Role2Creator","Role2User"]

$cat role3.json | jq -rc  '[.Tags[] | select(.Key == ("Name","ID","Creator","Manager","User")) | .Value]'
["Role3Name","Role3ID","Role3Creator","Role3Manager","Role3User"]

Can someone share with me how this can be done using jq.

Also, how can we enforce the order.

Thanks!

ikegami · Accepted Answer · 2021-06-29T05:01:07.833

The key (ha!) is

[ .[ $keys[] ] ]

Had you looked at other answers to questions relating to CSV, you might have noticed the first step taken is to get the list of keys. This is often done by collecting the keys of the input objects. (Example) In your case, you have a hard-coded list, so it's even simpler.

If you wanted actual CSV, you could use

jq -sr '
   [ "Name", "ID", "Creator", "Manager", "User" ] as $keys |
   (
      $keys,
      ( .[].Tags | from_entries | [ .[ $keys[] ] ] )
   ) |
   @csv
' role*.json

This produces

"Name","ID","Creator","Manager","User"
"Role1Name","Role1ID",,"Role1Manager","Role1User"
"Role2Name","Role2ID","Role2Creator",,"Role2User"
"Role3Name","Role3ID","Role3Creator","Role3Manager","Role3User"

jqplay

Without a header:

jq -r '.Tags | from_entries | [ .["Name","ID","Creator","Manager","User"] ] | @csv' role*.json

jqplay

To get the specific output you posted (which isn't CSV), you could use

jq -sr '
   [ "Name", "ID", "Creator", "Manager", "User" ] as $keys |
   (
      $keys,
      ( .[].Tags | from_entries | [ .[ $keys[] ] | . // "null" ] )
   ) |
   join(", ")
' role*.json

This produces

Name, ID, Creator, Manager, User
Role1Name, Role1ID, null, Role1Manager, Role1User
Role2Name, Role2ID, Role2Creator, null, Role2User
Role3Name, Role3ID, Role3Creator, Role3Manager, Role3User

jqplay

Without a header:

jq -r '.Tags | from_entries | [ .["Name","ID","Creator","Manager","User"] | . // "null" ] | join(", ")' role*.json

jqplay

Thanks https://stackoverflow.com/users/589924/ikegami – xbalaji Jun 29 '21 at 03:54 — xbalaji, Jun 29 '21 at 03:54

score 0 · Answer 2 · answered Jun 29 '21 at 03:55

0

Got an answer from another forum, might be useful for others

$jq -rc  '.Tags | from_entries | [.Name, .ID, .Creator, .Manager, .User]' role*.json
["Role1Name","Role1ID",null,"Role1Manager","Role1User"]
["Role2Name","Role2ID","Role2Creator",null,"Role2User"]
["Role3Name","Role3ID","Role3Creator","Role3Manager","Role3User"]

answered Jun 29 '21 at 03:55

xbalaji

982
7
16

That's neither CSV nor the format requested in the OP, though. Is there an error in the question or in the answer? – ikegami Jun 29 '21 at 04:31
Thanks https://stackoverflow.com/users/589924/ikegami. Your answer is helpful, I just wanted to post the other response. I agree it is not the same, thanks again. – xbalaji Jun 29 '21 at 04:55

jq - select objects and print null for missing

2 Answers2