Postgres JSONb including array to XML with key and value and value

Question

I have a table as below:

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"]}

Would like to convert it to :

 <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>

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;

However the array values displays as below:

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

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.

Ah, thanks for the example. I had feared at first that you expected to get <CompanyTag tagName="name" tagValue="abc" tagValue="def" …/>, which I guess is not possible. Repeated <CompanyTag>s however is easy. — Bergi
– Bergi, Commented Oct 14, 2020 at 14:18

Bergi · Accepted Answer · 2020-10-14 14:20:46Z

0

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)}

answered Oct 14, 2020 at 14:20

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bergi Over a year ago

@AArun Glad to have helped! You might want to accept the answers again.

Collectives™ on Stack Overflow

Postgres JSONb including array to XML with key and value and value

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related