PostgreSQL如何基于JSON列内的sku值关联两张表

• JOIN语法错误：USING关键字后需要用括号包裹关联字段，正确写法为USING(sku)
• JSON操作逻辑错误：根据你给出的product表sku字段存储示例[149461190]，该字段本身就是JSON数组，不需要通过-> 'ps_sku'取子键值
• 函数类型不匹配：jsonb_to_recordset是JSONB类型的专属处理函数，如果你的sku字段是JSON类型，需要替换为JSON类型对应的处理函数
• 运行环境不匹配：你收到的报错是Hive SQL的解析异常，说明你实际运行SQL的环境是Hive，而非PostgreSQL，PostgreSQL的原生JSON处理函数无法在Hive中直接运行

PostgreSQL环境运行方案 #

SELECT *
FROM product ps
-- sku为JSONB类型时使用此行
CROSS JOIN jsonb_array_elements_text(ps.sku) AS t(sku)
-- sku为JSON类型时替换为下面这行
-- CROSS JOIN json_array_elements_text(ps.sku) AS t(sku)
JOIN item v USING(sku);

如果你实际的sku存储结构为嵌套对象{"ps_sku": [149461190]}，将代码中的ps.sku替换为ps.sku -> 'ps_sku'即可。

Hive环境运行方案 #

SELECT *
FROM product ps
LATERAL VIEW explode(from_json(ps.sku, 'array<string>')) t AS sku
JOIN item v ON t.sku = v.sku;

如果sku字段存储的是数值类型数组，将from_json的第二个参数修改为array<bigint>即可。

内容的提问来源于stack exchange，提问作者Alexandra Salamatina