1

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.

2
  • What is the size of your JSON data? Also, if you need to do operations with these data, you may have a look to mongodb or other document databases. I believe that if you had your data in a mongo database, you wouldn't be thinking of doing these changes, the space would be the same, and you could see them with any of your two views (described as old and new). – thanasisp Oct 4 '20 at 22:55
  • Yeah future plan is to move towards some database but for now we need to reorganize it so that we don't have to duplicate id's everytime. Size is 25k, its not that big but we need to duplicate id's every time so thats why I wanted to reorganize it. – David Todd Oct 4 '20 at 23:52
1

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"
    ]
  }
}
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(.[0].clientId)} )
    ) |
     .productSettings |=
    ( group_by(.productId) |
      map( {productId: .[0].productId, entries: del(.[0].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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.