json - 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!

标签： jsoncsvexport-to-csvjq

Answer 1

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