Extract fields from postgres jsonb column

Question

I have a postgres table with jsonb column, which has the value as follows:

 id  |  messageStatus       |   payload
-----|----------------------|-------------
1    |    123               | {"commissionEvents":[{"id":1,"name1":"12","name2":15,"name4":"apple","name5":"fruit"},{"id":2,"name1":"22","name2":15,"name4":"sf","name5":"fdfjkd"}]}
2    |    124               | {"commissionEvents":[{"id":3,"name1":"32","name2":15,"name4":"sf","name5":"fdfjkd"},{"id":4,"name1":"42","name2":15,"name4":"apple","name5":"fruit"}]}
3    |    125               | {"commissionEvents":[{"id":5,"name1":"42","name2":15,"name4":"apple","name5":"fdfjkd"},{"id":6,"name1":"52","name2":15,"name4":"sf","name5":"fdfjkd"},{"id":7,"name1":"62","name2":15,"name4":"apple","name5":"fdfjkd"}]}

here payload column is a jsonb datatype, I want to write a postgres query to fetch name1 from commissionEvents where name4 = apple.

So my result will be like:

Since I was new to this jsonb, can anyone please suggest me some solution for it.

score 3 · Accepted Answer · 2019-07-11 09:38:39Z

3

You need to unnest all array elements, then you can apply a WHERE condition on that to filter out those with the desired name.

select t.id, x.o ->> 'name1'
from the_table t
  cross join lateral jsonb_array_elements(t.payload -> 'commissionEvents') as x(o)
where x.o ->> 'name4' = 'apple'

Online example: https://rextester.com/XWHG26387

edited Jul 11, 2019 at 9:38

answered Jul 11, 2019 at 9:17

user330315

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

2 Comments

kaviya .P Over a year ago

It returns "No operator matches the given name and argument type(s). You might need to add explicit type casts. SQL state: 42883 Character: 93" error at "=" symbol

user330315 Over a year ago

Ah, it wasn't clear that there is an array involved. See my edit

Collectives™ on Stack Overflow

Extract fields from postgres jsonb column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related