CODEWITHSUNDEEP
postgresql
Guerrilla
Guerrilla

Reputation: 14886

Querying jsonb in postgresql

I have a jsonb column called authors in my postgresql database table that has data like the following:

[{"id":134,"nameKey":"smith, john"},
 {"id":112,"nameKey":"bats, billy"}]

How do I return all rows from the table where the jsonb author column contains an author with id 112?

I need somethign like

select * from record
where authors -> id = 2

I also need to get by name also:

select * from gardner_record
where authors -> nameKey like '%john%'

I find the postgresql jsonb documentation almost impossible to make sense of. When I try to select by ID it says I am missing a cast. When I try to select by name it says the column doesn't exist.

How should I be querying this column?

Upvotes: 2

Views: 115

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

Use jsonb_array_elements

select t.*
    from t cross join jsonb_array_elements(authors) as j
    where j->>'id' = '112'



select t.*
    from t cross join jsonb_array_elements(authors) as j
    where j->>'nameKey' like '%john%'

DEMO

Upvotes: 0

Related Questions