Question 413689 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to use miller mlr or jq to combine two JSON arrays into one array?

I used to be a jq masochist, but I discovered mlr, which seems to offer the opportunity for more (yet different) pain.

I found a nice web site that allows me to issue curl commands without API keys, etc., to give me the data I am interested in. The problem being that my chosen tool for displaying the data (Visual Data Tools’ Datagraph) cannot parse the emitted JSON.

I could write something ugly using awk, sed, etc. but it would be more elegant to use something like jq or mlr to achieve this in one line.

The (x, y) pairs are in separate arrays. Is there a one-liner that will achieve the below?

A truncated example of the data that I need to transform. It seems that I could transform JSON to JSON, else JSON to CSV to achieve my goal.

{
    "btcusd": {
        "x": [
            1254700800000,
            1254787200000,
            1254873600000,
            1730419200000
        ],
        "y": [
            0.00076394,
            0.00076394,
            0.00088456,
            69513.1578978095
        ]
    },
    "xaubtc": {
        "x": [
            1254700800000,
            1254787200000,
            1254873600000,
            1719446400000
        ],
        "y": [
            1316202.8431552218,
            1359727.2037070976,
            1176008.410961382,
            0.037321851310868535
        ]
    }
}

An example of output data that Datagraph can parse (manually created):

[
   {
      "pair" : "btcusd",
      "x" : 1254700800000,
      "y" : 0.00076394
   },
   {
      "pair" : "btcusd",
      "x" : 1254787200000,
      "y" : 0.00076394
   },
   {
      "pair" : "btcusd",
      "x" : 1254873600000,
      "y" : 0.00088456
   },
   {
      "pair" : "btcusd",
      "x" : 1730419200000,
      "y" : 69513.1578978095
   },
   {
      "pair" : "xaubtc",
      "x" : 1254700800000,
      "y" : 1316202.84315522
   },
   {
      "pair" : "xaubtc",
      "x" : 1254787200000,
      "y" : 1359727.2037071
   },
   {
      "pair" : "xaubtc",
      "x" : 1254873600000,
      "y" : 1176008.41096138
   },
   {
      "pair" : "xaubtc",
      "x" : 1719446400000,
      "y" : 0.0373218513108685
   }
]

Which results in this kind of table in Datagraph

pair    y                   x
btcusd  0.00076394          1.2547008e12
btcusd  0.00076394          1.2547872e12
btcusd  0.00088456          1.2548736e12
btcusd  69513.1578978095    1.7304192e12
xaubtc  1.31620284315522e6  1.2547008e12
xaubtc  1.3597272037071e6   1.2547872e12
xaubtc  1.17600841096138e6  1.2548736e12
xaubtc  0.0373218513108686  1.7194464e12

Though this would be better (manually pretty-printed)

pair1   x1             y1                pair2   x2             y2
btcusd  1254700800000  0.00076394        xaubtc  1254700800000  1316202.8431552218
btcusd  1254787200000  0.00076394        xaubtc  1254787200000  1359727.2037070976
btcusd  1254873600000  0.00088456        xaubtc  1254873600000  1176008.410961382
btcusd  1730419200000  69513.1578978095  xaubtc  1719446400000  0.037321851310868535
Tags:

2

Answers


  1. 0

    You’re looking for something like this:

    to_entries | map({pair: .key} + (.value | transpose[] | {x: .[0], y: .[1]}))
    Login or Signup to reply.
  2. 0

    You can run

    jq '
  to_entries |
  map(
    .key as $pair |
    .value.x as $xs |
    .value.y as $ys |
    [range(0; $xs | length) | {pair: $pair, x: $xs[.], y: $ys[.]}]
  ) | add
' input.json

    to get

    [
  {
    "pair": "btcusd",
    "x": 1254700800000,
    "y": 0.00076394
  },
  {
    "pair": "btcusd",
    "x": 1254787200000,
    "y": 0.00076394
  },
  {
    "pair": "btcusd",
    "x": 1254873600000,
    "y": 0.00088456
  },
  {
    "pair": "btcusd",
    "x": 1730419200000,
    "y": 69513.1578978095
  },
  {
    "pair": "xaubtc",
    "x": 1254700800000,
    "y": 1316202.8431552218
  },
  {
    "pair": "xaubtc",
    "x": 1254787200000,
    "y": 1359727.2037070976
  },
  {
    "pair": "xaubtc",
    "x": 1254873600000,
    "y": 1176008.410961382
  },
  {
    "pair": "xaubtc",
    "x": 1719446400000,
    "y": 0.037321851310868535
  }
]
    1. to_entries: Converts the original JSON object into an array of key-value pairs, allowing us to access keys such as "btcusd" and "xaubtc" as named entries (pair).
    2. map(...): Processes each of the key-value pairs individually by applying the block of code inside.
    3. .key as $pair: Stores the key (e.g., "btcusd") in a variable called $pair for later use in each iteration.
    4. .value.x as $xs and .value.y as $ys: Stores the x and y arrays as temporary variables $xs and $ys, respectively.
    5. [range(0; $xs | length) | ... ]: Iterates over the indices of the x array (and implicitly y since they have the same length).
    6. {pair: $pair, x: $xs[.], y: $ys[.]}: Creates an object for each index that pairs the value from x and y along with the pair identifier (pair). The dot (.) represents the current index in the loop.
    7. add: Combines the results from all iterations of map into a single array.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search