Convert huge old json into new json format through some sort of linux commands?

Question

I have original json like this. In general, my json is very large. To make things simpler and easier to understand I have reduced the entries.

{
   "clientSettings":[
      {
         "clientId":12345,
         "key":"abc",
         "value":false
      },
      {
         "clientId":12345,
         "key":"def",
         "value":false
      },
      {
         "clientId":12345,
         "key":"ghi",
         "value":false
      },
      {
         "clientId":9876,
         "key":"lkmn",
         "value":false
      }
   ],
   "productSettings":[
      {
         "productId":11,
         "key":"jkl",
         "value":true
      },
      {
         "productId":11,
         "key":"mno",
         "value":true
      },
      {
         "productId":12,
         "key":"jkl",
         "value":true
      },
      {
         "productId":12,
         "key":"mno",
         "value":true
      }
   ],
   "customerSettings":[
      {
         "key":"enableData",
         "value":false
      },
      {
         "key":"minPriceValue",
         "value":"1.0"
      },
      {
         "key":"presentData",
         "value":"AEGIS"
      }
   ],
   "thothTest":{
      "9876":[
         "K"
      ],
      "5431":[
         "A",
         "L"
      ],
      "5123":[
         "L"
      ]
   },
   "osirisTest":{
      "7678":[
         "K"
      ]
   }
}

• In clientSettings json array we have clientId's and their keys/values. For single clientId, I can have multiple different keys and values. For example - 12345 clientId has different keys and values as shown above.
• Similarly for productSettings as well.
• But for customerSettings I just have different keys and values.
• For thothTest and osirisTest I don't have to do anything.

I am thinking to redesign the above json so that I don't have to duplicate clientId and productId for each keys and values. As of now my json is huge because I have lot of ids which are same but with different keys and values.

So I came up with below new json design which can represent same above json -

{
   "clientSettings":[
      {
         "clientId":12345,
         "entries":[
            {
               "key":"abc",
               "value":false
            },
            {
               "key":"def",
               "value":false
            },
            {
               "key":"ghi",
               "value":false
            }
         ]
      },
      {
         "clientId":9876,
         "entries":[
            {
               "key":"lkmn",
               "value":false
            }
         ]
      }
   ],
   "productSettings":[
      {
         "productId":11,
         "entries":[
            {
               "key":"jkl",
               "value":true
            },
            {
               "key":"mno",
               "value":true
            }
         ]
      },
      {
         "productId":12,
         "entries":[
            {
               "key":"jkl",
               "value":true
            },
            {
               "key":"mno",
               "value":true
            }
         ]
      }
   ],
   "customerSettings":[
      {
         "key":"enableData",
         "value":false
      },
      {
         "key":"minPriceValue",
         "value":"10.28"
      },
      {
         "key":"presentData",
         "value":"AEGIS"
      }
   ],
   "thothTest":{
      "9876":[
         "K"
      ],
      "5431":[
         "A",
         "L"
      ],
      "5123":[
         "L"
      ]
   },
   "osirisTest":{
      "7678":[
         "K"
      ]
   }
}

Problem Statement

Now given an old json - Is there any way to convert it to new json format through some sort of script or linux commands? Since my json is very huge so doing it one by one for each id's gonna take some time so was thinking if we can convert my old json to new json through some linux commands then it can speed up the process.

Answer 1

You can reshape your JSON using jq in many different ways. One way is to group by .clientId, and map to a new object, where .clientId will be outside the grouped array. Using group_by and map:

jq ' .clientSettings |=
    ( group_by(.clientId) |
      map( {clientId: .[0].clientId, entries: del(.[].clientId)} )
    ) |
     .productSettings |=
    ( group_by(.productId) |
      map( {productId: .[0].productId, entries: del(.[].productId)} )
    ) ' file.json

I have just copy-pasted the same command for the second object.

---

In case you need to do operations on large JSON frequently, I suggest you get familiar with and use mongodb or any similar document database, for example see this map-reduce page from the manual, it is what you are doing now, without the reduce part. All these operations can be faster than parsing a json file, syntax is similar to simple javascript.

Answer 2

JSON processing is best done with a tool that understands JSON, like jq or Python. Here's a Python script which does what you need:

#! /usr/bin/env python3

import json
import sys
from collections import defaultdict


def combine(data, key, id_key):
    new_settings = defaultdict(list)
    for setting in data[key]:
        # Remove the ID from the setting and add it to the list of settings for that ID
        new_settings[setting.pop(id_key)].append(setting)
    # arrange the new settings in the desired format and overwrite the old settings
    data[key] = [{id_key: key, "entries": values} for key, values in new_settings.items()]


data = json.load(sys.stdin)
combine(data, "clientSettings", "clientId")
combine(data, "productSettings", "productId")
print(json.dumps(data))

Feed the JSON into standard input and use the output:

$ ./process.py < old.json | jq
{
  "clientSettings": [
    {
      "clientId": 12345,
      "entries": [
        {
          "key": "abc",
          "value": false
        },
        {
          "key": "def",
          "value": false
        },
        {
          "key": "ghi",
          "value": false
        }
      ]
    },
    {
      "clientId": 9876,
      "entries": [
        {
          "key": "lkmn",
          "value": false
        }
      ]
    }
  ],
  "productSettings": [
    {
      "productId": 11,
      "entries": [
        {
          "key": "jkl",
          "value": true
        },
        {
          "key": "mno",
          "value": true
        }
      ]
    },
    {
      "productId": 12,
      "entries": [
        {
          "key": "jkl",
          "value": true
        },
        {
          "key": "mno",
          "value": true
        }
      ]
    }
  ],
  "customerSettings": [
    {
      "key": "enableData",
      "value": false
    },
    {
      "key": "minPriceValue",
      "value": "1.0"
    },
    {
      "key": "presentData",
      "value": "AEGIS"
    }
  ],
  "thothTest": {
    "9876": [
      "K"
    ],
    "5431": [
      "A",
      "L"
    ],
    "5123": [
      "L"
    ]
  },
  "osirisTest": {
    "7678": [
      "K"
    ]
  }
}

Answer 3

I'm answering with what is essentially the same solution as to what thanasisp posted, but reformulated to use a helper function, groupmap, to do the grouping and mapping of the contents of the clientSettings and productSettings arrays.

def groupmap($key):
        group_by(.[$key]) |
        map( { ($key): first[$key], entries: map(del(.[$key])) } );

.clientSettings  |= groupmap("clientId") |
.productSettings |= groupmap("productId")

With this in script, you would run it using

jq -f script file.json

---

The following was not asked for, but since several arrays contains "entries objects" (key and value), we can convert all of these to key: value entries instead:

def groupmap($key):
        group_by(.[$key]) |
        map( { ($key): first[$key], entries: map(del(.[$key])) } );

.clientSettings   |= (groupmap("clientId")  | map(.entries |= from_entries)) |
.productSettings  |= (groupmap("productId") | map(.entries |= from_entries)) |
.customerSettings |= from_entries

Given these modifications, your result would now look like this:

{
   "clientSettings": [
      {
         "clientId": 9876,
         "entries": { "lkmn": false }
      },
      {
         "clientId": 12345,
         "entries": { "abc": false, "def": false, "ghi": false }
      }
   ],
   "customerSettings": { "enableData": false, "minPriceValue": "1.0", "presentData": "AEGIS" },
   "osirisTest": {
      "7678": [ "K" ]
   },
   "productSettings": [
      {
         "entries": { "jkl": true, "mno": true },
         "productId": 11
      },
      {
         "entries": { "jkl": true, "mno": true },
         "productId": 12
      }
   ],
   "thothTest": {
      "5123": [ "L" ],
      "5431": [ "A", "L" ],
      "9876": [ "K" ]
   }
}