Postgres JSONb to XML with tag_name and tag_value

Question

I am new to Postgres XML functions. 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": "gefd"}

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="gefd"/>
 </Company>

Was able to convert the first two column easily using below but the JSONb has the toughest part:

#SELECT xmlelement(name Company, xmlattributes(id as id, field1 as field)) from comp_emp;;
 <Company id="123" field="a"/>
 <Company id="456" field="b"/>
 <Company id="789"/>
 <Company id="098" field="c"/>

Please guide me in the right direction.

Bergi · Accepted Answer · 2020-10-10 23:29:44Z

3

Building on @Georges Martin's solution, I wouldn't use a lateral join and a grouping clause, but rather just a nested subquery:

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;

^{(online demo)}

edited Oct 10, 2020 at 23:29

answered Oct 10, 2020 at 23:09

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

3 Comments

AArun Over a year ago

Unfortunately if the attributes is an array like below, instead of returning each value, it extracts as a single value: If the attribute value is : {"age": "2", "name": ["abc", "def", "ghi"]} it returns the value as : tagValue="["abc", "def", "ghi"]"/>

Bergi Over a year ago

@AArun You haven't mentioned that case before, I'd suggest you ask a new question about it and describe the expected result there.

AArun Over a year ago

Thank you. Have asked again with the updated values under stackoverflow.com/questions/64354263/…

Georges Martin · Accepted Answer · 2020-10-10 23:06:51Z

2

This is a partial solution as I forgot the field1 column but this:

SELECT 
 XMLELEMENT(
  NAME "Company", 
  XMLATTRIBUTES(id AS id), 
  XMLAGG(
   XMLELEMENT(
    NAME "CompanyTag", 
    XMLATTRIBUTES(
     (attr).key AS "tagName", 
     (attr).value AS "tagValue"
    )
   )
  )
 ) 
FROM (
 SELECT 
  id, JSONB_EACH_TEXT(attributes) AS attr 
 FROM comp_emp
) AS sub 
GROUP BY id;

already returns:

<Company id="098">
  <CompanyTag tagName="name" tagValue="gefd"/>
</Company>
<Company id="456">
  <CompanyTag tagName="age" tagValue="2"/>
  <CompanyTag tagName="name" tagValue="abcdef"/>
</Company>
<Company id="123">
  <CompanyTag tagName="age" tagValue="1"/>
  <CompanyTag tagName="place" tagValue="TX"/>
</Company>

edited Oct 10, 2020 at 23:06

answered Oct 10, 2020 at 22:53

Georges Martin

1,2181 gold badge9 silver badges18 bronze badges

Collectives™ on Stack Overflow

Postgres JSONb to XML with tag_name and tag_value

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related