0

If I run the following query:

select headers from database_name.schema_name.table;

The output is a JSON array like this:

enter image description here

How do I query PostgreSQL 12 to return only the "From" nodes?

Expected result:

*From*
Me
Him
His
Improve this question
3
  • Are you sure you are using Postgres? Because from db.all_messages.message is invalid in Postgres Commented Apr 24, 2020 at 21:13
  • Yes, PostgreSQL 12 and sorry, it should really be the 'select headers from database_name.schema_name.table;'. I've changed the post to reflect that. Commented Apr 24, 2020 at 22:44
  • Postgres does not allow to reference a database like that. Commented Apr 25, 2020 at 6:13

1 Answer 1

Reset to default
1

You need to unnest the array and then access the elements by index:

select x.h ->> 1 as "From"
from t
  cross join jsonb_array_elements(t.headers) as x(h)
where x.h ->> 0 = 'From'

Online example

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.