Join Postgresql tables on json array columns

Question

I have two tables in postgresql with json array columnstableA.B and tableB.B. How to join these tables on these json columns? i.e.

select tableA.id, tablA.A, tableB.id,tableA.B, tableB.name
from tableA, tableB 
where tableA.B = tableB.B 

--tableA--
id | A     | B                 
1  | 36464 | ["874746", "474657"] 
2  | 36465 | ["874748"] 
3  | 36466 | ["874736", "474654"] 

--tableB--
id | name  | B                 
1  | john  | ["8740246", "2474657"] 
2  | mary  | ["874748","874736"] 
3  | clara | ["874736", "474654"]

This question is not clearly defined without your version of Postgres and proper table definitions showing data types and constraints. Most importantly: `json` or `jsonb`? Are empty arrays or NULL values allowed and considered equal? — Erwin Brandstetter, Jun 25 '16 at 23:35

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

Actually, with the data type jsonb in Postgres 9.4 or later, this becomes dead simple. Your query would just work (ugly naming convention, code and duplicate names in the output aside).

CREATE TEMP TABLE table_a(a_id int, a int, b jsonb);
INSERT INTO table_a VALUES
  (1, 36464, '["874746", "474657"]')
, (2, 36465, '["874748"]')
, (3, 36466, '["874736", "474654"]');

CREATE TEMP TABLE table_b(b_id int, name text, b jsonb);
INSERT INTO table_b VALUES
  (1, 'john' , '["8740246", "2474657"]')
, (2, 'mary' , '["874748","874736"]')
, (3, 'clara', '["874736", "474654"]');

Query:

SELECT a_id, a, b.*
FROM   table_a a
JOIN   table_b b USING (b);  -- match on the whole jsonb column

That you even ask indicates you are using the data type json, for which no equality operator exists:

How to query a json column for empty objects?

You just didn't mention the most important details.

The obvious solution is to switch to jsonb.

Answer to your comment

is it possible to flatten out b into new rows rather than an array?

Use jsonb_array_elements(jsonb) or jsonb_array_elements_text(jsonb) in a LATERAL join:

SELECT a_id, a, b.b_id, b.name, b_array_element
FROM   table_a a
JOIN   table_b b USING (b)
     , jsonb_array_elements_text(b) b_array_element

This returns only rows matching on the whole array. About LATERAL:

What is the difference between LATERAL and a subquery in PostgreSQL?

If you want to match on array elements instead, unnest your arrays before you join.
The whole setup seems to be in dire need of normalization.

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:11

score 0 · Answer 2 · answered Jun 25 '16 at 20:29

WITH tableA AS
  (SELECT 1 AS id,
          36464 AS A,
          '["874746", "474657"]'::jsonb AS B
   UNION SELECT 2 AS id,
                36465 AS A,
                '["874748"]'::jsonb AS B
   UNION SELECT 3 AS id,
                36466 AS A,
                '["874736", "474654"]'::jsonb AS B),
     tableB AS
  ( SELECT 1 AS id,
           'john' AS name,
           '["8740246", "2474657"]'::jsonb AS B
   UNION SELECT 2 AS id,
                'mary' AS name,
                '["874748", "874736"]'::jsonb AS B
   UNION SELECT 3 AS id,
                'clara' AS name,
                '["874736", "474654"]'::jsonb AS B)
SELECT *
FROM tableA
inner join tableB using(B);

Gives you

          b           | id |   a   | id | name  
----------------------+----+-------+----+-------
 ["874736", "474654"] |  3 | 36466 |  3 | clara

Isn't it what you expect?

Thank you @Guillaume Thomas is it possible to flatten out b into new rows rather than an array? — ArchieTiger, Jun 25 '16 at 21:43

Join Postgresql tables on json array columns

2 Answers2

Answer to your comment