CODEWITHSUNDEEP
postgresqljsonb
HoTicE
HoTicE

Reputation: 573

How to filter JSONB list

I have table with this data:

id       | 1
accounts | [{"id": "100", "properties": [{"id": "PR-001", "name": "name1"}, {"id": "PR-002", "name": "name2"}]}]
property | "PR-001"

accounts is jsonb field.

I need to get all property.name where accounts.property.id equals property with SELECT.

I am using Postgres 9.5

Upvotes: 1

Views: 244

Answers (1)

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4572

You can use LEFT LATERAL JOIN:

WITH tbl (id,accounts,property) AS (
  SELECT 1, '{"id": "100", "properties": [{"id": "PR-001", "name": "name1"}, {"id": "PR-002", "name": "name2"}]}'::jsonb, 'PR-001'::text
  )
SELECT t.id, acc->>'name'
FROM tbl t
LEFT JOIN LATERAL jsonb_array_elements(t.accounts->'properties') acc ON (acc->>'id' = t.property)

Upvotes: 1

Related Questions