CODEWITHSUNDEEP
jsonjq
user2556342
user2556342

Reputation: 55

jq - Map Object values using startswith()

I got two .json files, the first one contains the data:

data.json

[
 {"ID_EXT_LARGE":"aaa_1234411","xy":"xyz"},
 {"ID_EXT_LARGE":"bbb_1474411","xy":"cfg"},
 {"ID_EXT_LARGE":"ccc_8944411","xy":"drt"},
 {"ID_EXT_LARGE":"aaa_1234411","xy":"kai"}
]

The other one contains the IDs:

id_array.json

[
 {"ID_EXT":"aaa","ID_WEB":30,"ID_ACC":"one"},
 {"ID_EXT":"bbb","ID_WEB":40,"ID_ACC":"two"},
 {"ID_EXT":"ccc","ID_WEB":50,"ID_ACC":"three"}
]

Now I try to get the "ID_WEB" and "ID_ACC" propertie into the objects of data.json, using the mapping of ID_EXT_LARGE and ID_EXT.

The problem is, that ID_EXT only contains the first characters of ID_EXT_LARGE. Expected result - (should be the extended data.json file):

data.json

[
 {"ID_EXT_LARGE":"aaa_1234411","ID_WEB":30,"ID_ACC":"one","xy":"xyz"},
 {"ID_EXT_LARGE":"bbb_1474411","ID_WEB":40,"ID_ACC":"two","xy":"cfg"},
 {"ID_EXT_LARGE":"ccc_8944411","ID_WEB":50,"ID_ACC":"three","xy":"drt"},
 {"ID_EXT_LARGE":"aaa_1234411","ID_WEB":30,"ID_ACC":"one","xy":"kai"}
]

I tried it for ID_WEB and was thinking of something like this, (the for loop was just an idea):

script.jq

  def getIDWEB(id_array);
        for i ....
          if ."ID_EXT_LARGE"|startswith(id_array[i].ID_EXT) then id_array[i].ID_WEB  end
        end  
    ;

  def setIDWEB(id_array):
     .ID_WEB = getIDWEB(id_array)
    ;

  ($id_array) as $id_array
  | map(setIDWEB($id_array))

Probably I am thinking too complicated and this is actually a one-liner?

Upvotes: 0

Views: 657

Answers (1)

jq170727
jq170727

Reputation: 14715

Here is an approach which builds a "table" object from id_array.json. This function creates the table:

def maketable:
  reduce $id_array[] as $i (
    {}
  ; .[$i.ID_EXT] = ($i | {ID_WEB,ID_ACC})
  )
;

With the sample id_array.json in $id_array this returns an object like

{
  "aaa": {
    "ID_WEB": 30,
    "ID_ACC": "one"
  },
  "bbb": {
    "ID_WEB": 40,
    "ID_ACC": "two"
  },
  "ccc": {
    "ID_WEB": 50,
    "ID_ACC": "three"
  }
}

This function takes an object from data.json and returns the corresponding lookup key for the table:

def getkey: .ID_EXT_LARGE | split("_")[0] ;

e.g. given 

{"ID_EXT_LARGE":"aaa_1234411","xy":"xyz"}

it returns

"aaa"

With these two functions the result output can be generated with:

  maketable as $idtable
| map( . + $idtable[ getkey ] )

Here is a script which puts everything together and uses sponge to update data.json:

#!/bin/bash
jq -M --argfile id_array id_array.json '

    def maketable:
      reduce $id_array[] as $i (
        {}
      ; .[$i.ID_EXT] = ($i | {ID_WEB,ID_ACC})
      )
    ;
    def getkey: .ID_EXT_LARGE | split("_")[0] ;

      maketable as $idtable
    | map( . + $idtable[ getkey ] )

' data.json | sponge data.json

Here is data.json after a sample run:

[
  {
    "ID_EXT_LARGE": "aaa_1234411",
    "xy": "xyz",
    "ID_WEB": 30,
    "ID_ACC": "one"
  },
  {
    "ID_EXT_LARGE": "bbb_1474411",
    "xy": "cfg",
    "ID_WEB": 40,
    "ID_ACC": "two"
  },
  {
    "ID_EXT_LARGE": "ccc_8944411",
    "xy": "drt",
    "ID_WEB": 50,
    "ID_ACC": "three"
  },
  {
    "ID_EXT_LARGE": "aaa_1234411",
    "xy": "kai",
    "ID_WEB": 30,
    "ID_ACC": "one"
  }
]

Note that as peak points out maketable could be replaced with

def maketable: INDEX($id_array[]; .ID_EXT) | map_values(del(.ID_EXT)) ;

if the more general INDEX builtin (definition below) is available.

def INDEX(stream; idx_expr):  
  reduce stream as $row (
    {}
  ; .[$row|idx_expr| if type != "string" then tojson else . end] |= $row
  )
;

Upvotes: 1

Related Questions