CODEWITHSUNDEEP
arraysjsonpostgresqljsonb
Fastbrainx
Fastbrainx

Reputation: 89

Postgres jsonb select specific array fields

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.

Upvotes: 1

Views: 159

Answers (1)

klin
klin

Reputation: 121919

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

Upvotes: 2

Related Questions