Fill arrays in the first input with elements from the second based on common field

user_15241280 0
Viewed 37

I have two files and I would need to merge the elements of the second file into an object array in the first file based on searching the reference field.

The first file:

[
    {
        "reference": 25422,
        "order_number": "10_1",
        "details" : []
    },
    {
        "reference": 25423,
        "order_number": "10_2",
        "details" : []
    }
]

The second file:

[
    {
        "record_id" : 1,
        "reference": 25422,
        "row_description": "descr_1_0"
    },
    {
        "record_id" : 2,
        "reference": 25422,
        "row_description": "descr_1_1"
    },
    {
        "record_id" : 3,
        "reference": 25423,
        "row_description": "descr_2_0"
    }
]

I would like to get:

[
    {
        "reference": 25422,
        "order_number": "10_1",
        "details" : [
            {
                "record_id" : 1,
                "reference": 25422,
                "row_description": "descr_1_0"
            },
            {
                "record_id" : 2,
                "reference": 25422,
                "row_description": "descr_1_1"
            }
        ]
    },
    {
        "reference": 25423,
        "order_number": "10_2",
        "details" :[
            {
                "record_id" : 3,
                "reference": 25423,
                "row_description": "descr_2_0"
            }
        ]
    }
]

Below is my code in es_func.jq file launched by this command:

jq -n --argfile f1 es_file1.json --argfile f2 es_file2.json -f es_func.jq

INDEX($f2[] ; .reference) as $details 
| $f1 
| map( ($details[.reference|tostring]| .row_description) as $vn 
| if $vn then .details = [{"row_description" : $vn}] else . end)

I get the result only for the last record in 25422 reference with "row description": "descr_1_1" and not have "row_description": "descr_1_0"

[
  {
    "reference": 25422,
    "order_number": "10_1",
    "details": [
      {
        "row_description": "descr_1_1"
      }
    ]
  },
  {
    "reference": 25423,
    "order_number": "10_2",
    "details": [
      {
        "row_description": "descr_2_0"
      }
    ]
  }
]

I think I'm close to the solution but something is still missing. Thank you

jq json
2 Answers

This would be way easier if you used reduce instead.

jq 'reduce inputs[] as $rec (INDEX(.reference);
  .[$rec.reference | tostring].details += [$rec]
) | map(.)' es_file1.json es_file2.json

Online demo

user_102486780

Here's a straightforward, reduce-free solution:

jq '
  group_by(.reference)
  | INDEX(.[]; .[0]|.reference|tostring) as $dict
  | input
  | map_values(. + {details: $dict[.reference|tostring]})
' 2.json 1.json
user_9973580
Related