Postgres extract json data with brackets and apostrophe-CodePudding

it is my first time seeing this kind of data inside a json and I have no idea how to extract the values inside of it. Basically it is a django log and I am doing a report that will extract all the old_value and new_value.

Here is the sample json data:

{"root['data_adjustment']['timestamp']": {"new_value": "2022-03-21 10:37", "old_value": "2022-03-21 10:34"}

So far here is how I did it:

SELECT
data->'root['data_adjustment']['timestamp']' -> 'new_value' AS new_value,
data->'root['data_adjustment']['timestamp']' -> 'old_value' AS old_value

However, I am producing an error on it pointing to the apostrophe. I tried skipping it using double apostrophe but it produces error again.

CodePudding user response：

Your quotes are all over the place. E.g. the second quote (after the opening bracket) closes the string again. Therefore you end up with a "data_adjustment" instruction - and so on ...

Your initial idea, using double quotes, is the right approach here:

select data -> 'root[''data_adjustment''][''timestamp'']' -> 'new_value' as new_value,
       data -> 'root[''data_adjustment''][''timestamp'']' -> 'old_value' as old_value