How to find Postgresql Json array elements of one table matches different column values of another table

Question

I have a table X with one column of type jsonb.

Jsonb contains json array - "cities":["aaaa","bbbb","cccc"].

Postgresql 9.4 provides jsonb operators to get json array elements using '->'

There is another table Y with column cities.
Y
a b cities
   aaaa
   bbbb
   cccc

I want to display select Y.a, Y.b from Y, X only if X.jsonb->cities is present in Y.cities.

maybe sqlfiddle? to see tables and be able to play with statement?.. — Vao Tsun
– Vao Tsun, Commented Aug 20, 2015 at 5:32

Craig Ringer · Accepted Answer · 2015-08-20 13:22:16Z

-1

This is done with a lateral join over the json_array_elements (or in this case json_array_elements_text since y.cities is presumably text-typed) function. You didn't provide a full sample schema, so I'll hand-wave some untested SQL to give you the idea.

select *
from x
cross join json_array_elements_text(x.cities) AS x_cities(city)
inner join y on (x_cities.city = y.cities);

If you're going to use json you're going to need to get very good with lateral joins.

In general I'm seeing a lot of people using json where it's completely unnecessary and a simple relational modelling would be more appropriate. Think about whether you really need to do this. In this case it seems like if you must use an array, a native PostgreSQL text[] array would be better, but you should probably model it with a join-table instead.

answered Aug 20, 2015 at 13:22

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

6 Comments

Sindhu Shashidhar Over a year ago

Y.cities - varchar. X.State is jsonb type. X.state has cities as json array. I cannot apply json_array_elements_text or json_array_elements on jsonb.

Craig Ringer Over a year ago

@SindhuShashidhar use jsonb_arra u_elements_text .

Sindhu Shashidhar Over a year ago

thanks Craig. jsonb column is of type json object. It has 6 json arrays. One of the array is cities. If I try to fetch all json arrays by jsonb_array_elements_text(X.state), it gives error "cannot extract elements from an object".

Sindhu Shashidhar Over a year ago

1) {"state":["{"cityA":[],"cityB":[],"cityC":[],"cityD":[]}"]} How to get all arrays elements and compare with table Y?

Craig Ringer Over a year ago

@SindhuShashidhar Your sample data and description did not indicate that. If you can't solve it please post a new accurate question with sample data as CREATE TABLE and INSERT statements

|

Collectives™ on Stack Overflow

How to find Postgresql Json array elements of one table matches different column values of another table

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related