Add or Update a field in one JSON file from another JSON file based on matching field

Question

I have two JSON files a.json and b.json. The contents in a.json file is a JSON object and inside b.json its an array.I want to add/update status field in each mappings in a.json by retrieving the value from b.json file.
a.json:

{
  "title": 25886,
  "data": {
    "request": {
      "c": 46369,
      "t1": 1562050127.376641
    },
  },
  "rs": {
    "mappings": {
      "12345": {
        "id": "12345",
        "name": "test",
        "customer_id": "11228",
      },
      "45678": {
        "id": "45678",
        "name": "abc",
        "customer_id": "11206",
      }
    }
}}

b.json:

[
  {
    "status": "pending",
    "extra": {
      "name": "test"
    },
    "enabled": true,
    "id": "12345"
  },
  {
    "status": "not_started",
    "extra": {
      "name": "abc"
    },
    "enabled": true,
    "id": "45678"
  }
]

Below is my expected output:

{
  "title": 25886,
  "data": {
    "request": {
      "c": 46369,
      "t1": 1562050127.376641
    },
  },
  "rs": {
    "mappings": {
      "12345": {
        "id": "12345",
        "name": "test",
        "customer_id": "11228",
        "status":"pending"
      },
      "45678": {
        "id": "45678",
        "name": "abc",
        "customer_id": "11206",
        "status":"not_started"
      }
    }
}}

In this expected JSON file we have status field whose value is retrieved from b.json file based on a matching id value. How to do this using jq ?

peak · Accepted Answer · 2019-07-02 08:31:25Z

1

For the purposes of this problem, b.json essentially defines a dictionary, so for simplicity, efficiency and perhaps elegance, it make sense to start by using the builtin function INDEX to create the relevant dictionary:

INDEX( $b[] | {id, status}; .id )

This assumes an invocation of jq along the lines of:

jq --argfile b b.json -f update.jq a.json

(Yes, I know --argfile has been deprecated. Feel free to choose another way to set $b to the contents of b.json.)

Now, to perform the update, it will be simplest to use the "update" operator, |=, in conjunction with map_values. (Feel free to check the jq manual :-)

Putting everything together:

INDEX( $b[] | {id, status}; .id ) as $dict
| .rs.mappings |= map_values( .status = $dict[.id].status )

answered Jul 2, 2019 at 8:31

peak

119k21 gold badges185 silver badges218 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dks551 Over a year ago

jq --argfile b b.json -f a.json | INDEX( $b[] | {id, status}; .id ) as $dict | .rs.mappings |= map_values( .status = $dict[.id].status ) -bash: syntax error near unexpected token `$b[]'

dks551 Over a year ago

jq --argfile b b.json -f update.jq a.json | INDEX( $b[] | {id, status}; .id ) as $dict | .rs.mappings |= map_values( .status = $dict[.id].status ) -bash: syntax error near unexpected token `$b[]'

Aaron Over a year ago

@dks551 if you're passing the jq script as a parameter to the jq command you should 1) remove the -f flag which specifies a file to read the jq script from ; 2) quote the jq script with single-quotes so that bash doesn't expand $vars ; 3) put the a.json file after the jq script. Something like

jq --argfile b b.json 'INDEX( $b[] | {id, status}; .id ) as $dict | .rs.mappings |= map_values( .status = $dict[.id].status )' a.json

Collectives™ on Stack Overflow

Add or Update a field in one JSON file from another JSON file based on matching field

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related