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

Question

我有一个如下表：

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能够使用以下查询转换非数组：

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提取了最外层的值而导致的。有其他选择吗？

请引导我朝正确的方向走。

Answer 1

如果处理数组，则需要额外的jsonb_array_elements_text来提取值。完成横向连接：

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)

^{(在线演示，带完整查询(}