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

Normalize JSON data

Given the following JSON structure

{
    "data": [
        {
            "id": "000000000014444A",
            "teams": [
                "000000000011AE74"
            ]
        },
        {
            "id": "000000000014434B",
            "teams": [
                "000000000011AE74",
                "00000000001583CE",
                "000000000010B4D7"
            ]
        }
    ]
}

How can I use jq to structure the data as

[
    {
        "id": "000000000014444A",
        "team": "000000000011AE74"
    },
    {
        "id": "000000000014434B",
        "team": "000000000011AE74"
    },
    {
        "id": "000000000014434B",
        "team": "00000000001583CE"
    },
    {
        "id": "000000000014434B",
        "team": "000000000010B4D7"
    }
]
Tags:

2

Answers


  1. 0

    Take the .data array, and map each item to an object. Use .teams[] to iterate without capturing the items in a separate array, so the output is multiplicated:

    jq '.data | map({id, team: .teams[]})'

    [
  {
    "id": "000000000014444A",
    "team": "000000000011AE74"
  },
  {
    "id": "000000000014434B",
    "team": "000000000011AE74"
  },
  {
    "id": "000000000014434B",
    "team": "00000000001583CE"
  },
  {
    "id": "000000000014434B",
    "team": "000000000010B4D7"
  }
]

    Demo

    Login or Signup to reply.
  2. 0

    There are a several ways to get the result that you want. One of them is described in @pmf’s answer.

    This is another way:

    jq '[ .data[] | { id, team: .teams[] } ]'

    How it works:

    1. .data extracts the property data from the input (the input is an object); the value of property data is an array;

    2. [] splits the array into items; the output of .data[] consists of multiple values, each value is one of the items of .data; the output of .data[] consists of two objects;

    3. | – the next filters apply to each input value independent of other values; the input values are the values produced by .data[];

    4. { id, team: .teams[] } – the output of this filter is an object ({ ... }) whose properties are id and team;

      • id is a shorthand of id: .id; the key (id) is the name of the property in the output object; the value (.id) is a filter that extracts the property id of each input object (the input objects have the properties id and teams);
      • team: .teams[] produce the property team in the output object; its value is the value of property teams from the input object; teams is an array in the input object but [] after .team split the value of teams into individual items; the expression .teams[] produce multiple output values from one input value; it produces one result for the object with id equal to "000000000014444A" and three results for the object with id equal to "000000000014434B".

      All in all, the expression .data[] | { id, team: .teams[] } produce four results as output.

    5. The outer [ and ] wrap the results produced by the enclosed pipeline into an array. The array (which is the output of the entire program) contains the four items generated by the inner expression. It is the output that you expect.

    See it on the jq playground.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search