CODEWITHSUNDEEP
jsonmergekeyjq
user2480552
user2480552

Reputation: 49

jq 1.5: Combining keys from one array with their values that are in a different array

I'd like to take some key values that are found in one array, and match them with their corresponding values in a different array. For reference I'm using jq-1.5

I've got some data from the quandl api where I'm pulling some stock data. For example, the following pulls down some json data.

curl https://www.quandl.com/api/v3/datatables/WIKI/PRICES.json?ticker=FB&qopts.columns=date,close,high,low&api_key=myapikeyblahblah

The data is as follows, though I've removed some of the redundant data;

{
  "datatable": {
    "data": [
      ["2012-05-18", 38.2318, 45.0, 38.0],
      ["2012-05-21", 34.03, 36.66, 33.0],
      ["2012-05-22", 31.0, 33.59, 30.94],
      ["2017-06-22", 153.4, 154.55, 152.91],
      ["2017-06-23", 155.07, 155.2, 152.65]
    ],
    "columns": [{
      "name": "date",
      "type": "Date"
    }, {
      "name": "close",
      "type": "BigDecimal(34,12)"
    }, {
      "name": "high",
      "type": "BigDecimal(34,12)"
    }, {
      "name": "low",
      "type": "BigDecimal(34,12)"
    }]
  },
  "meta": {
    "next_cursor_id": null
  }
}

I'm looking to match the "keys" from the .datatable.columns[$index1].name with the "values" in .datatable.data[1] and so on with each iterating index value. I'm looking to get an output like the following;

[
  {
    "date": "2012-05-18",
    "close": 38.2318,
    "high": 45.0,
    "low": 38.0
  },
  {
    "date": "2012-05-21",
    "close": 34.03,
    "high": 36.66,
    "low": 33.0
  },
  {
    "date": "2012-05-22",
    "close": 31.0,
    "high": 33.59,
    "low": 30.94
  },
  {
    "date": "2017-06-22",
    "close": 153.4,
    "high": 154.55,
    "low": 152.91
  },
  {
    "date": "2017-06-23",
    "close": 155.07,
    "high": 155.2,
    "low": 152.65
  }
]

So far i've played around with the idea of counting up the index, but most of my solutions so far have been fairly verbose, and i'm finding myself stepping out of jq to sed/awk etc for something that I imagine is easy in jq.

Upvotes: 1

Views: 358

Answers (2)

RomanPerekhrest
RomanPerekhrest

Reputation: 92854

Python solution:

combine_keys.py script:

import sys, json

data = json.load(open(sys.argv[1], 'r'))
columns = [o['name'] for o in data['datatable']['columns']]
result = json.dumps([dict(zip(columns, i)) for i in data['datatable']['data']], indent=4)
print(result)

Usage:

python combine_keys.py input.json

The output:

[
    {
        "low": 38.0,
        "date": "2012-05-18",
        "close": 38.2318,
        "high": 45.0
    },
    {
        "low": 33.0,
        "date": "2012-05-21",
        "close": 34.03,
        "high": 36.66
    },
    {
        "low": 30.94,
        "date": "2012-05-22",
        "close": 31.0,
        "high": 33.59
    },
    {
        "low": 152.91,
        "date": "2017-06-22",
        "close": 153.4,
        "high": 154.55
    },
    {
        "low": 152.65,
        "date": "2017-06-23",
        "close": 155.07,
        "high": 155.2
    }
]

Upvotes: 0

peak
peak

Reputation: 116740

Here's a helper function to make the solution easy to understand. It converts the input array into an object, on the assumption that headers is an array of strings to be used as key names:

def objectify(headers):
  [headers, .] | transpose | map( { (.[0]): .[1] } ) | add;

A solution is now straightforward:

.datatable
| (.columns | map(.name)) as $headers
| .data
| map( objectify($headers) )

Upvotes: 3

Related Questions