Question 167931 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

Transform JSON into a table and arrange columns in a special manner using jq

There is JSON like below:

{
    "data": [
        {
            "a": 1,
            "d": 2,
            "c": 3,
            "b": 4
        },
        {
            "a": 5,
            "d": 6,
            "c": 7,
            "b": 8,
            "e": 9
        }
    ]
}

I want to transform it to the table like this:

c   d   a   b   e
3   2   1   4   <absent>
7   6   5   8   9

So:

  • Columns c and d are moved to the beginning of the table;
  • All other columns are sorted alphabetically.
Tags:

2

Answers


  1. 0

    If the key names are known, you could pre-define their order in a variable as an array, then use that for the header as well as the columns in each row. @tsv will turn arrays into tab-separated text, so make sure to use jq -r or jq --raw-output.

    ["c","d","a","b","e"] as $keys
| $keys, (.data[] | [.[$keys[]]])
| @tsv

    c   d   a   b   e
3   2   1   4   
7   6   5   8   9

    Demo

    If the <absent> string was intended to be inserted literally, use // to provide an alternative content:

    ["c","d","a","b","e"] as $keys
| $keys, (.data[] | [.[$keys[]] | . // "<absent>"])
| @tsv

    c   d   a   b   e
3   2   1   4   <absent>
7   6   5   8   9

    Demo

    I know column names but there are a lot of columns (about 30) and I don’t want to list all of them in the filters.

    Then scan the keys first, make a unique list, drop c and d, and prepend them. This is your dynamic $keys variable.

    .data
| (map(to_entries[].key | select(IN("c", "d") | not)) | ["c","d"] + unique) as $keys
| $keys, (.[] | [.[$keys[]] | . // "<absent>"])
| @tsv

    c   d   a   b   e
3   2   1   4   <absent>
7   6   5   8   9

    Demo

    Login or Signup to reply.
  2. 0 
    .data
| ["c","d"] as $cd
| ($cd + (add|keys - $cd)) as $keys
| $keys, [.[][$keys[]] // "<absent>"]
| @tsv
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search