时间戳

首页 > 解决方案 > 检查 Postgres json 数组是否与另一个数组相交

postgresql - 检查 Postgres json 数组是否与另一个数组相交

问题描述

是否有一个 postgres jsonb 运算符来检查一组元素中的任何一个元素是否在另一组元素中？

例如：

if ['a', 'b', 'c'] in ['c', 'd', 'e'] = True because c in 2nd set
if ['f', 'g', 'h'] in ['g', 'h', 'i'] = True because of g and h in 2nd set

标签： postgresqlsqlalchemy

解决方案

您可以使用 (when using ) 或 (when using ) 扩展 JSON 数组，或使用( when jsonb_array_elementsusingjsonbjson_array_elementsjson )或(when using )直接扩展为文本，然后使用 将它们转换为 PostgreSQL 数组。jsonb_array_elements_textjsonbjson_array_elements_textjsonarray_agg

WITH json_data(x, y) AS (
    VALUES ('["a", "b", "c"]'::jsonb, '["c", "d", "e"]'::jsonb),
           ('["a", "b", "c"]'::jsonb, '["g", "h", "i"]'::jsonb)
)
, array_data AS (
    SELECT x, (SELECT array_agg(e) AS arr FROM jsonb_array_elements_text(x) e) AS xarr,
           y, (SELECT array_agg(e) AS arr FROM jsonb_array_elements_text(y) e) AS yarr
    FROM json_data
)
SELECT x, y, xarr, yarr, xarr && yarr AS with_intersection FROM array_data

这产生了这些结果：

        x        |        y        |  xarr   |  yarr   | with_intersection
-----------------+-----------------+---------+---------+-------------------
 ["a", "b", "c"] | ["c", "d", "e"] | {a,b,c} | {c,d,e} | t
 ["a", "b", "c"] | ["g", "h", "i"] | {a,b,c} | {g,h,i} | f
(2 rows)

推荐阅读