Postgresql Convert SQL XML Coding to SQL JSON Coding

Question

How to convert XML SQL Coding to JSON SQL Coding. Example:

SELECT XMLELEMENT(NAME "ORDER", XMLFOREST(PURCHASE_ORDER AS OD_NO)) AS "XMLELEMENT" FROM 
TBL_SALES

Now how to convert this XMLELEMENT & XMLFOREST into JSON functions. Please help me. Do we have equivalent XMLELEMENT/XMLFOREST in JSON functions.

xml:

<order><OD_NO>4524286167</OD_NO><order_date>2020-06-15</order_date><sales_office>CH</sales_office></order>

json:

{ "OD_NO": "4524286167", "order_date": "2020-06-15", "sales_office": "CH" }

what JSON output you are expecting from your query as comparing XML with JSON will not solve your purpose because both are different — Akhilesh Mishra
– Akhilesh Mishra, Commented Oct 9, 2020 at 5:20
@AkhileshMishra xml: <order><OD_NO>4524286167</OD_NO><order_date>2020-06-15</order_date><sales_office>CH</sales_office></order> json: { "OD_NO": "4524286167", "order_date": "2020-06-15", "sales_office": "CH" } How to write SQL Coding to get result in json — Karthik
– Karthik, Commented Oct 9, 2020 at 5:32

Akhilesh Mishra · Accepted Answer · 2020-10-09 05:40:30Z

1

Here row_to_json will do the thing.

You can write your query like below:

select row_to_json(x) from 
(select purchase_order "OD_NO", order_date, sales_office from  tbl_sales ) x

If You want to aggregate all the results in a single JSON Array use JSON_AGG with row_to_json:

select json_agg(row_to_json(x)) from 
(select purchase_order "OD_NO", order_date, sales_office from  tbl_sales ) x

DEMO

answered Oct 9, 2020 at 5:40

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

Comments

Stefanov.sm · Accepted Answer · 2020-10-09 10:30:18Z

0

These Postgresql functions
json_build_object(VARIADIC "any") and
jsonb_build_object(VARIADIC "any")
are semantically close to XMLELEMENT and very convenient for 'embroidering' of whatever complex JSON you may need. Your query might look like this:

select json_build_object
(
  'OD_NO', order_number, -- or whatever the name of the column is
  'order_date', order_date,
  'sales_office', sales_office
) as json_order
from tbl_sales;

I do not think that there is a XMLFOREST equivalent however.

edited Oct 9, 2020 at 10:30

answered Oct 9, 2020 at 8:25

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Collectives™ on Stack Overflow

Postgresql Convert SQL XML Coding to SQL JSON Coding

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related