需要从Postgresql表中动态选择一个JSON数组元素

需要从Postgresql表中动态选择一个JSON数组元素

json

在Postgres 9.3中

在9.3版中很难做到这一点，因为缺少有用的功能。

方法1

嵌套

LEFT JOIN LATERAL

（干净且符合标准），将双引号从

json

铸造后修剪到

text

。请参阅下面的链接。

SELECt DISTINCT ON (1)       t.id, t.name, d.lastFROM   tbl tLEFT   JOIN LATERAL (  SELECt ('[' || d::text || ']')::json->>0 AS last  FROM   json_array_elements(t.data) d  ) d ON d.last <> t.nameORDER  BY 1, row_number() OVER () DESC;

尽管这可行，但我从未见过失败，但未嵌套元素的顺序取决于未记录的行为。见下面的链接！使用@pozs在注释中提供的表达式
改进了从

json

到的转换。仍然有点破旧，但应该是安全的。

text

方法2

SELECt DISTINCT ON (1)       id, name, NULLIF(last, name) AS lastFROM (   SELECt t.id, t.name        ,('[' || json_array_elements(t.data)::text || ']')::json->>0 AS last        , row_number() OVER () AS rn   FROM   tbl t   ) subORDER  BY 1, (last = name), rn DESC;

• 清单中的巢状

  SELECt

  （非标准）。

• rn

  并行附加行号（）（更可靠）。
• 转换成

  text

  上面的样子。
• 子句中的表达式

  (last = name)

  对

  ORDER BY

  匹配的名称进行最后排序（但在NULL之前）。因此，仅当没有其他名称可用时，才选择匹配的名称。下面的最后一个链接。在

  SELECT

  列表中，

  NULLIF

  将匹配的名称替换为

  NULL

  ，得到与上述相同的结果。

SQL提琴。

json

或

jsonb

在Postgres 9.4中

第9.4页提供了所有必要的改进：

SELECT DISTINCT ON (1)       t.id, t.name, d.lastFROM   tbl tLEFT   JOIN LATERAL json_array_elements_text(data) WITH ORDINALITY d(last, rn)       ON d.last <> t.nameORDER  BY d.rn DESC;

使用

jsonb_array_elements_text()

了

jsonb

。其他都一样。

手册中的json /jsonb函数