0

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.

Sindhu Shashidhar
  • 79
  • 2
  • 7

1 Answers1

-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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 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. – Sindhu Shashidhar Aug 20 '15 at 15:36
  • @SindhuShashidhar use jsonb_arra u_elements_text . – Craig Ringer Aug 21 '15 at 00:37
  • 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 Aug 21 '15 at 15:30
  • 1) {"state":["{"cityA":[],"cityB":[],"cityC":[],"cityD":[]}"]} How to get all arrays elements and compare with table Y? – Sindhu Shashidhar Aug 21 '15 at 15:36
  • @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 – Craig Ringer Aug 21 '15 at 23:56
  • What about **[PostgreSql : Json Array to Rows using Lateral Join](https://stackoverflow.com/questions/61709474/postgresql-json-array-to-rows-using-lateral-join)**? –  May 10 '20 at 09:17