Postgres JSONb 包括带有键值和值的 XML 数组

本文介绍了Postgres JSONb 包括带有键值和值的 XML 数组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表格如下:

id (VARCHAR) | field1 (text) | attributes (jsonb) --------------+---------------+---------------------------------- 123 | a | {"age": "1", "place": "TX"} 456 | b | {"age": "2", "name": "abcdef"} 789 | | 098 | c | {"name": ["abc", "def", "ghi"]}

想将其转换为:

<Company id="123" field="a"> <CompanyTag tagName="age" tagValue="1"/> <CompanyTag tagName="place" tagValue="TX"/> </Company> <Company id="456" field="b"> <CompanyTag tagName="age" tagValue="2"/> <CompanyTag tagName="name" tagValue="abcdef"/> </Company> <Company id="789"/> <Company id="098" field="c"> <CompanyTag tagName="name" tagValue="abc"/> <CompanyTag tagName="name" tagValue="def"/> <CompanyTag tagName="name" tagValue="ghi"/> </Company>

在@bergi 和@Georges Martin 的帮助下Post 能够使用以下查询转换非数组:

With help of @bergi and @Georges Martin under Post was able to convert the non array using below query:

SELECT XMLELEMENT( NAME "Company", XMLATTRIBUTES(id AS id, field1 AS field), (SELECT XMLAGG( XMLELEMENT( NAME "companyTag", XMLATTRIBUTES( attr.key AS "tagName", attr.value AS "tagValue" ) ) ) FROM JSONB_EACH_TEXT(attributes) AS attr) ) FROM comp_emp;

但是数组值显示如下:

<Company id="098" field="c"> <CompanyTag tagName="name"tagValue="[&quot;abc&quot;, &quot;def&quot;, &quot;ghi&quot;]"/>

我不想在查询中特别提及键(tagName")，因为这可能会有所不同.假设这是由于 JSONB_EACH_TEXT 提取最外层值引起的.有没有替代品?

I do not want to mention the key ("tagName") specifically in the query as this may vary. Assuming that this is caused due to JSONB_EACH_TEXT extracting the outermost values. Is there an alternative?

请引导我走向正确的方向.

Please guide me in the right direction.

推荐答案

如果您正在处理数组，则需要额外的 jsonb_array_elements_text 来提取值.使用横向连接完成:

You'll need an extra jsonb_array_elements_text extracting the values if you're dealing with an array. Done with a lateral join:

SELECT XMLAGG( XMLELEMENT( NAME "CompanyTag", XMLATTRIBUTES( attr.key AS "tagName", values.element AS "tagValue" ) ) ) FROM jsonb_each(attributes) AS attr, LATERAL jsonb_array_elements_text(CASE jsonb_typeof(attr.value) WHEN 'array' THEN attr.value ELSE jsonb_build_array(attr.value) END) AS values(element)

(在线演示，附完整查询)

(online demo, with complete query)