"Transpose"/"Rotate"/"Flip" JSON elements

user_1409374 0
Viewed 475

I would like to "transpose" (not sure that's the right word) JSON elements.

For example, I have a JSON file like this:

{
  "name": {
    "0": "fred",
    "1": "barney"
  },
  "loudness": {
    "0": "extreme",
    "1": "not so loud"
  }
}

... and I would like to generate a JSON array like this:

[
  {
    "name": "fred",
    "loudness": "extreme"
  },
  {
    "name": "barney",
    "loudness": "not so loud"
  }
]

My original JSON has many more first level elements than just "name" and "loudness", and many more names, features, etc.

For this simple example I could fully specify the transformation like this:

$ echo '{"name":{"0":"fred","1":"barney"},"loudness":{"0":"extreme","1":"not so loud"}}'| \
> jq '[{"name":.name."0", "loudness":.loudness."0"},{"name":.name."1", "loudness":.loudness."1"}]'

[
  {
    "name": "fred",
    "loudness": "extreme"
  },
  {
    "name": "barney",
    "loudness": "not so loud"
  }
]

... but this isn't feasible for the original JSON.

How can jq create the desired output while being key-agnostic for my much larger JSON file?

jq transpose json
3 Answers

Yes, transpose is an appropriate word, as the following makes explicit.

The following generic helper function makes for a simple solution that is completely agnostic about the key names, both of the enclosing object and the inner objects:

# Input: an array of values
def objectify($keys):
  . as $in | reduce range(0;length) as $i ({}; .[$keys[$i]] = $in[$i]);

Assuming consistency of the ordering of the inner keys

Assuming the key names in the inner objects are given in a consistent order, a solution can now obtained as follows:

keys_unsorted as $keys
| [.[] | [.[]]] | transpose
| map(objectify($keys))

Without assuming consistency of the ordering of the inner keys

If the ordering of the inner keys cannot be assumed to be consistent, then one approach would be to order them, e.g. using this generic helper function:

def reorder($keys):
  . as $in | reduce $keys[] as $k ({}; .[$k] = $in[$k]);

or if you prefer a reduce-free def:

def reorder($keys): [$keys[] as $k | {($k): .[$k]}] | add;

The "main" program above can then be modified as follows:

keys_unsorted as $keys
| (.[$keys[0]]|keys_unsorted) as $inner
| map_values(reorder($inner))
| [.[] | [.[]]] | transpose
| map(objectify($keys))
Caveat

The preceding solution only considers the key names in the first inner object.

user_9973580

This is a bit hairy, but it works:

. as $data | 
reduce paths(scalars) as $p (
  [];
  setpath(
    [ $p[1] | tonumber, $p[0] ];
    ( $data | getpath($p) ) 
  ) 
)

First, capture the top level as $data because . is about to get a new value in the reduce block.

Then, call paths(scalars) which gives a key path to all of the leaf nodes in the input. e.g. for your sample it would give ["name", "0"] then ["name", "1"], then ["loudness", "0"], then ["loudness", "1"].

Run a reduce on each of those paths, starting the reduction with an empty array.

For each path, construct a new path, in the opposite order, with numbers-in-strings turned into real numbers that can be used as array indices, e.g. ["name", "0"] becomes [0, "name"].

Then use getpath to get the value at the old path in $data and setpath to set a value at the new path in . and return it as the next . for the reduce.

At the end, the result will be

[
  {
    "name": "fred",
    "loudness": "extreme"
  },
  {
    "name": "barney",
    "loudness": "not so loud"
  }
]

If your real data structure might be two levels deep then you would need to replace [ $p[1] | tonumber, $p[0] ] with a more appropriate expression to transform the path. Or maybe some of your "values" are objects/arrays that you want to leave alone, in which case you probably need to replace paths(scalars) with something like paths | select(length == 2).

user_1529480

Building upon Peak's solution, here is an alternative based on group_by to deal with arbitrary orders of inner keys.

keys_unsorted as $keys
| map(to_entries[])
| group_by(.key)
| map(with_entries(.key = $keys[.key] | .value |= .value))

Using paths is a good idea as pointed out by Hobbs. You could also do something like this :

[ path(.[][]) as $p | { key: $p[0], value: getpath($p), id: $p[1] } ]
| group_by(.id)
| map(from_entries)
user_33546610
Related