How to convert data format in JSON using SED

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.

"createDate":"01/02/2018"

dd/mm/yyyy

yyyy-mm-dd

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

sed

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".

"createDate"

A sample JSON object looks like this:


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



Any help is greatly appreciated.

2 Answers
2

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

createDate

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:

"createDate":

==> 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



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.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.