Postgres jsonb select specific array fields

Question

I have PostgreSQL 10.2 table "snapshots" with jsonb column named "data"

{
  "entries": [
    {
      "userName": "John",
      "age": "15"
    },
    {
      "userName": "Max",
      "age": "42"
    }]
}

Need a query to select only userNames from entries in the array. I tried this

select data->'entries'->>'userName' from snapshots;

but of course it's not returning values that I need.

klin · Accepted Answer · 2018-12-15 12:02:45Z

2

Unnest the json array with the function jsonb_array_elements() used in a lateral join:

select item->>'userName'
from snapshots
cross join jsonb_array_elements(data->'entries') as item

answered Dec 15, 2018 at 12:02

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Answer 1