Postgres extract json data with brackets and apostrophe

March 21, 2022

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.

>Solution :

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