Converting timestamp object in json to timestamp as a string

user_7206830 0
Viewed 388

I have a json exported from Mongodb . It shows timestamp in a different format "timestamp":{"$date":"2020-08-01T00:00:00Z"}} . I want to convert this timestamp object to a string like "timestamp":"2020-08-01T00:00:00Z" .

I have done this using sed like s/\{\"[$]date\":\"(\S{20})\"}/"\1"/g;. However , the length of the timestamp may vary if milliseconds are added to the time .Eg : "timestamp":{"$date":"2020-08-01T00:00:00.123Z"}} would be transformed with s/\{\"[$]date\":\"(\S{23})\"}/"\1"/g; , since the length of timestamp string is 23 characters. I want to convert this into a single transformation step where any length for the string would be good.

I can do this with jq too but that will need to read every line , store the value in a variable and use awk to change the value . This would be time consuming as compared to sed . So looking for a different solution.

A good answer would be really appreciated. Thanks

jq mongodb datetime-format awk json
2 Answers

Consider:

walk( if type == "object" and has("$date")
      then .["$date"] else . end )

This risks losing data, so you might want to consider similar alternatives, e.g. a stronger precondition, or a precondition using the expected key name. Or you might want to raise an error if the "date" object is not as expected.

user_9973580

I got it worked out . This answer is for those who are looking for a similar solution .

cat test.json | jq ' .location.timestamp = .location.timestamp."$date"' | sponge test.json

I didn't know how to use sponge, sponge is great!

user_72068300
Related