sql - 在 json 数组列上加入 Postgresql 表

Question

我在 postgresql 中有两个表，其中包含 json 数组列 tableA.B 和 tableB.B。如何在这些 json 列上加入这些表？即

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"] 

Answer 1

实际上，对于 Postgres 9.4 或更高版本中的数据类型 jsonb，这变得非常简单。您的查询将正常工作(除了输出中的丑陋命名约定、代码和重复名称)。

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"]');

查询:

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

你甚至问表明你正在使用数据类型 json，不存在相等运算符:

• How to query a json column for empty objects?

你只是没有提到最重要的细节。

显而易见的解决方案是切换到 jsonb。

---

回答your comment

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

使用jsonb_array_elements(jsonb) or jsonb_array_elements_text(jsonb)在 LATERAL 连接中:

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

这只返回与整个数组匹配的行。关于横向:

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

如果您想改为匹配数组元素，请在加入之前取消嵌套数组。
整个设置似乎急需 normalization .