我有一个表格如下:
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 数组
发布评论