Postgres JSONb 包括带有键值和值的 XML 数组 [英] Postgres JSONb including array to XML with key and value and value
问题描述
我有一个表格如下:
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="["abc", "def", "ghi"]"/>
我不想在查询中特别提及键(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)
这篇关于Postgres JSONb 包括带有键值和值的 XML 数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!