Flatten / normalize json array of objects with jq

user_2343739 0
Viewed 252

I have a large json array of objects. Each object contains a foreignKeyId, a url, (optionally) a urlMirror1, and (optionally) a urlMirror2.

Here's a sample:

[
  {
    "foreignKeyId": 1,
    "url": "https://1-url.com"
  },
  {
    "foreignKeyId": 2,
    "url": "https://2-url.com",
    "urlMirror1": "https://2-url-mirror-1.com",
  },
  {
    "foreignKeyId": 3,
    "url": "https://3-url.com",
    "urlMirror1": "https://3-url-mirror-1.com",
    "urlMirror2": "https://3-url-mirror-2.com"
  }
}

I want to normalize this json to something like below:

[
  {
    "foreignKeyId": 1,
    "primariness": 1,
    "url": "https://1-url.com"
  },
  {
    "foreignKeyId": 2,
    "primariness": 1,
    "url": "https://2-url.com",
  },
  {
    "foreignKeyId": 2,
    "primariness": 2,
    "url": "https://2-url-mirror-1.com",
  },
  {
    "foreignKeyId": 3,
    "primariness": 1,
    "url": "https://3-url.com"
  },
  {
    "foreignKeyId": 3,
    "primariness": 2,
    "url": "https://3-url-mirror-1.com",
  },
  {
    "foreignKeyId": 3,
    "primariness": 3,
    "url": "https://3-url-mirror-2.com"
  }
}

Is there a way to do something like this using jq? If not, any other suggestions to accomplish this quickly without writing too much custom code? This only needs to be run one time, so any kind of hacky one-off solution could work (bash script, etc.).

Thanks!

Update: primariness should be derived from the key names (url => 1, urlMirror1 => 2, urlMirror2 => 3. Order of the keys inside any given object is insignificant. There is a fixed number of mirrors (e.g., there is never a urlMirror3).

jq relational-database json
3 Answers

Here is a simple script with hardcoded number of mirrors and primariness. Hope it will do the trick.

jq '
    map(
        { foreinKeyId } +
        (
            { primariness: 1, url },
            (.urlMirror1 // empty | { primariness: 2, url: . }),
            (.urlMirror2 // empty | { primariness: 3, url: . })
        )
    )
' input.json
user_33546610

Given that OP has limited the query from generic down to a more specific criteria, the answer provided by @luciole75w is the best (most probably), refer to that one.

Now, for @oguzismail, this is a generic jtc approach (which will handle an arbitrary number of "urlMirror"s) made of 3 JSON transformation steps (updated solution):

<file.json jtc -w'<foreignKeyId>l:<f>v[-1]<urlM>L:<u>v[^0]' \
               -i'{"url":{{u}},"foreignKeyId":{f}}' /\
               -w'[foreignKeyId]:<f>q:<p:0>v[^0][foreignKeyId]:<f>s:[-1]<p>I1' \
               -i'{"primeriness":{{p}}}' /\
               -pw'<urlM>L:' -tc
[
   { "foreignKeyId": 1, "primeriness": 1, "url": "https://1-url.com" },
   { "foreignKeyId": 2, "primeriness": 1, "url": "https://2-url.com" },
   { "foreignKeyId": 3, "primeriness": 1, "url": "https://3-url.com" },
   { "foreignKeyId": 2, "primeriness": 2, "url": "https://2-url-mirror-1.com" },
   { "foreignKeyId": 3, "primeriness": 2, "url": "https://3-url-mirror-1.com" },
   { "foreignKeyId": 3, "primeriness": 3, "url": "https://3-url-mirror-2.com" }
]
bash $

Explanation and visualization:

- all the 3 steps can be observed in a "slow-mo":
1. for each found "foreignKeyId" and each "urlMirror" found within the same record extend (insert into) the array with {"url":... , "foreignKeyId": ...}:

<file.json jtc -w'<foreignKeyId>l:<f>v[-1]<urlM>L:<u>v[^0]' \
               -i'{"url":{{u}},"foreignKeyId":{f}}' -tc
[
   { "foreignKeyId": 1, "url": "https://1-url.com" },
   { "foreignKeyId": 2, "url": "https://2-url.com", "urlMirror1": "https://2-url-mirror-1.com" },
   { "foreignKeyId": 3, "url": "https://3-url.com", "urlMirror1": "https://3-url-mirror-1.com", "urlMirror2": "https://3-url-mirror-2.com" },
   { "foreignKeyId": 2, "url": "https://2-url-mirror-1.com" },
   { "foreignKeyId": 3, "url": "https://3-url-mirror-1.com" },
   { "foreignKeyId": 3, "url": "https://3-url-mirror-2.com" }
]
bash $

2. now insert "primariness": N records based on the index of the occurrence of the foreignKeyId:

<file.json jtc -w'<foreignKeyId>l:<f>v[-1]<urlM>L:<u>v[^0]' \
               -i'{"url":{{u}},"foreignKeyId":{f}}' /\
               -w'[foreignKeyId]:<f>q:<p:0>v[^0][foreignKeyId]:<f>s:[-1]<p>I1' \
               -i'{"primeriness":{{p}}}' -tc
[
   { "foreignKeyId": 1, "primeriness": 1, "url": "https://1-url.com" },
   { "foreignKeyId": 2, "primeriness": 1, "url": "https://2-url.com", "urlMirror1": "https://2-url-mirror-1.com" },
   { "foreignKeyId": 3, "primeriness": 1, "url": "https://3-url.com", "urlMirror1": "https://3-url-mirror-1.com", "urlMirror2": "https://3-url-mirror-2.com" },
   { "foreignKeyId": 2, "primeriness": 2, "url": "https://2-url-mirror-1.com" },
   { "foreignKeyId": 3, "primeriness": 2, "url": "https://3-url-mirror-1.com" },
   { "foreignKeyId": 3, "primeriness": 3, "url": "https://3-url-mirror-2.com" }
]
bash $

3. and final step (-pw'<urlM>L:') - rid of all redundant "urlMirror"s records.

Optionally: if there's a requirement to sort all the records within the top array as per the OP's example, then this additional step will do: -jw'[foreignKeyId]:<>g:[-1]'

PS. it so happens that I'm also a developer of the jtc unix tool

user_113858080

Here's a general solution, that is, it will handle arbitrarily many urlMirrors.

For the sake of clarity, let's begin by defining a helper function that emits a stream of {foreignKeyId, primariness, url} objects for a single input object:

def primarinesses:
  {foreinKeyId} +
    ({primariness:1, url},
     (to_entries[]
      | (.key | capture( "^urlMirror(?<n>[0-9]+)")) as $n
      | {primariness: ($n.n | tonumber + 1), url : .value } )) ;

The solution is then simply:

[.[] | primarinesses]

which can also be written with less punctuation as:

map(primarinesses)
user_9973580
Related