Flatten / normalize json array of objects with jq

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

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)

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