How to convert data format in JSON using SED

Question

I have a huge NDJSON file wherein one field is "createDate":"01/02/2018". It is in dd/mm/yyyy format and I need to convert it into yyyy-mm-dd format.

I can do this using sed on a small input using the below command:

echo 28/02/2018 | sed 's,\([0-9][0-9]\)/\([0-1][0-9]\)/\([1-2][0-9][0-9][0-9]\),\3-\2-\1,'

However, I'm unable to find a solution wherein I have to do this in a JSON file where this value is under a key with name "createDate".

A sample JSON object looks like this:

{
    "pushNotificationEnabled": "true",
    "createDate": "11/08/2018",
    "email": null,
    "photoUrl": null
  }

Any help is greatly appreciated.

Answer 1

Your command works on your sample JSON object! You might want to restrict its action to the createDate field:

sed '/"createDate":/s,\([0-9][0-9]\)/\([0-1][0-9]\)/\([1-2][0-9][0-9][0-9]\),\3-\2-\1,' input.json

This will only affect lines containing the "createDate": tag:

==> input.json <==
{
    "pushNotificationEnabled": "true",
    "createDate": "11/08/2018",
    "modifyDate": "31/08/2018",
    "email": null,
    "photoUrl": null
  }
$ sed '/"createDate":/s,\([0-9][0-9]\)/\([0-1][0-9]\)/\([1-2][0-9][0-9][0-9]\),\3-\2-\1,' input.json
{
    "pushNotificationEnabled": "true",
    "createDate": "2018-08-11",
    "modifyDate": "31/08/2018",
    "email": null,
    "photoUrl": null
  }

Answer 2

Assuming each object in the NDJSON file will fit comfortably in memory, an invocation of jq along the following lines should do the job, regardless of how large the file itself is, because jq will (by default) only read in one JSON entity at a time:

jq '.createDate |=
    sub("^(?<m>[0-9]*)/(?<d>[0-9]*)/(?<y>[0-9]*)"; "\(.y)-\(.m)-\(.d)")' input.json

Although jq might be slightly less efficient than sed for the task, it does understand JSON.