How to get a pretty print JSON or XML with SQL using Json_Object... etc

Question

I am Writing below query to generate a JSON:

SELECT 
json_object ('Product_id' value PROID,'Product_name' value PRONAME) 
from Product

Which Actually results in JSON in a CLOB field and the Output is not Pretty. The output looks like below:

{"Product_id":"PRD001","Product_name":"Dairy"}

The output i am expecting should be something like below:

{
 "Product_id":"PRD001",
 "Product_name":"Dairy"
}

This Pretty format JSON should be stored in an IFS path.

Could anyone help me to get this done. Any inputs in this area is much appreciated.

You are going to have to write a program to do this. As stated, this question is way to broad for the Q&A format since there are a million ways to write a program to do this, and several challenges that you will have to overcome. Start writing your program, and when you have specific questions, then come back here and ask those. — jmarkmurphy, Aug 02 '19 at 13:58
You definitely do *not* have to write your own program to do this; there are plenty of JSON pretty-printers out there already. However, I don't know of any that are in the form of an ILE program or service program. They would be easiest to run in PASE, though you could also conceivably find one implemented in C and adapt the source code for ILE C. — John Y, Aug 02 '19 at 15:09
But if you want it to come out of DB2 like that, you will have to write it. YAJL might be able to help, but I have never used it. Or maybe you could write some python code to do it? I'm sure @JohnY could help with that, but as far as something that you could just call in your SQL, not going to happen without some sort of programming. — jmarkmurphy, Aug 02 '19 at 15:16
@jmarkmurphy - What I meant was you don't have to write the pretty-printer yourself, which is what it sounded to me as though you were saying in your first comment. Of course you have to write *something* yourself. But that can amount to fairly simple wrappers or a thin "glue" layer. — John Y, Aug 02 '19 at 16:07

0 Answers0