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

jq – JSON convert keys into objects

I’m trying to reformat a big JSON from an Excel to JSON conversion into a valid JSON for import.
In my research I came across jq and hope some one more deep into can help me.

I have an Array of Employees like this one

{
  "Employees": [
    {
      "firstName": "First",
      "lastName": "Last",
      "birthday": "1990-01-01",
      "account": "true",
      "address.street": "Street",
      "address.addition": "1",
      "address.zipCode": "1000",
      "address.city": "Vienna",
      "address.region": "AT",
      "contactDetails.1.phone": "+431234567",
      "contactDetails.1.mail": "[email protected]",
      "organisations.0.id": 1,
      "departments.0.id": 1,
      "roles.0.id": 1
    },
  ]
}

the output should look as follows for each Employee

{
  "Employees": [
    {
      "firstName": "First",
      "lastName": "Last",
      "birthday": "1990-01-01",
      "account": "true",
      "address": {
        "street": "Street",
        "addition": "1",
        "zipCode": "1000",
        "city": "Vienna",
        "region": "AT"
      },
      "contactDetails": [
        {
          "phone": "",
          "mail": ""
        },
        {
          "phone": "+431234567",
          "mail": "[email protected]"
        }
      ],
      "organisations": [
        {
          "id": 1
        }
      ],
      "departments": [
        {
          "id": 1
        }
      ],
      "roles": [
        {
          "id": 1
        }
      ]
    }
  ]
}

is this possible with jq or any ideas how to achieve this?

Tags:

2

Answers


  1. 0

    Here’s one approach using to_entries to access keys and values. The .key is split at dots using /, the parts looking like numbers converted to numbers representing array indices, and used as path to set the .value with setpath. Using a reduce iteration successively builds up the result objects, for each item in the .Employees array. The only caveat is that the zeroth item in the .contactDetails array (unaddressed in the input data, but necessary to include higher indices) is not automatically populated with the keys of its sibling item (and empty string values, i.e. {"phone": "", "mail": ""}) which you would have to provide separately.

    .Employees[] |= (
  reduce to_entries[] as $e (null;
    setpath($e.key / "." | map(tonumber? // .); $e.value)
  )
)

    {
  "Employees": [
    {
      "firstName": "First",
      "lastName": "Last",
      "birthday": "1990-01-01",
      "account": "true",
      "address": {
        "street": "Street",
        "addition": "1",
        "zipCode": "1000",
        "city": "Vienna",
        "region": "AT"
      },
      "contactDetails": [
        null,
        {
          "phone": "+431234567",
          "mail": "[email protected]"
        }
      ],
      "organisations": [
        {
          "id": 1
        }
      ],
      "departments": [
        {
          "id": 1
        }
      ],
      "roles": [
        {
          "id": 1
        }
      ]
    }
  ]
}

    Demo

    Login or Signup to reply.
  2. 0

    Here’s another approach using streaming. Streaming produces an array for each leaf-value. The path (also an array) is the first element in the array and the leaf-value is the second element in the array. Splitting the dotted path elements to produce more path elements creates the structure you are looking for. After processing the paths, fromstream reconstructs the desired JSON output.

    N.B.: Missing array elements produce null elements in the output.

    fromstream(
  tostream
  |[(.[0] | map((splits("\.")? // .) | (tonumber? // .))), (.[1] // empty)]
)

    Example output:

    {
  "Employees": [
    {
      "firstName": "First",
      "lastName": "Last",
      "birthday": "1990-01-01",
      "account": "true",
      "address": {
        "street": "Street",
        "addition": "1",
        "zipCode": "1000",
        "city": "Vienna",
        "region": "AT"
      },
      "contactDetails": [
        null,
        {
          "phone": "+431234567",
          "mail": "[email protected]"
        }
      ],
      "organisations": [
        {
          "id": 1
        }
      ],
      "departments": [
        {
          "id": 1
        }
      ],
      "roles": [
        {
          "id": 1
        }
      ]
    }
  ]
}

    Try it on jqplay.org.

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

Back To Top
Search