Parse jsonb field - PostgreSQL

Question

I have a field that is of type jsonb in a PostgreSQL database.
Example:

{"timestamp":"2016-12-14T04:15:04.836Z","receiptResult":{"status":"successful","timestamp":"2016-12-14T04:15:04.739Z","notes":"Customer Accepted"}}

How can I only return the "notes" in a select statement, I've tried:

SELECT data::json->>'notes' as notes

But nothing is returned, if I use:

SELECT data::json->'receiptResult' as notes;

It returns:

{"status":"successful","timestamp":"2016-114T04:15:04.739Z","notes":"Customer Accepted"}

But I only need the text after "notes".

Michel Milezzi · Accepted Answer · 2017-06-28 14:47:28Z

1

Inside key receiptResult has another JSON object, you cannot access it in top-level. Try this:

WITH sample AS (
    SELECT '{"timestamp":"2016-12-14T04:15:04.836Z","receiptResult":{"status":"successful","timestamp":"2016-12-14T04:15:04.739Z","notes":"Customer Accepted"}}'::jsonb AS my_column
)
SELECT my_column->'receiptResult'->>'notes' FROM sample;

As you can see, -> operator returns value as a JSONB and ->> operator returns as a text.

More info here.

edited Jun 28, 2017 at 14:47

answered Jun 28, 2017 at 14:42

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ruan Labuschagne Over a year ago

Thanks for the answer Michel, out of the office today, will test as soon as possible.

Michel Milezzi Over a year ago

No problem. Regards!

Michel Milezzi Over a year ago

Glad to help you @RuanLabuschagne

Collectives™ on Stack Overflow

Parse jsonb field - PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related