Possible to use db2 json functions for partial json

[TheZenbudda]

I want to modularize my data retrieval queries that generate json into service program procedures. However, I do not know how to get db2 to generate json value pairs without the object or array terminators ({} []). Here is what I mean by this.


Sample json result

{
  "customer" : 123456,
  "customerName" : "ACME Shipping",
  "customerPhone" : 5555555555,
  "orders": [
    {
      "orderNumber" : "5555",
      "orderTotal" : 123.45
    }, {
      "orderNumber" : "4444",
      "orderTotal" : 321.00
    }
  ]
} 

I envision that I can create a srvpgm procedure to return customer info, ie. getCustomerInfoJson(customerNumber), and another procedure to return orders (ie. getCustomerOrders(customerNumber), then concatenate the results. However, I cannot seem to figure out how to get db2 to not include terminators {} when building json. The only way I have figured out how to do this is to code a 2nd parameter to remove the object terminators, ie. getCustomerInfo(customerNumber:removeTerminators).

I realize I "could" do this:

{
"customerInfo" : {
"customerNumber" : 123456,
"customerName" : "ACME Shipping",
"customerPhone" : 5555555555
},
"orders": [
{
"orderNumber" : "5555",
"orderTotal" : 123.45
}, {
"orderNumber" : "4444",
"orderTotal" : 321.00
}
]
}
[/PHP]

With the above example, could return an entire customerInfo object, then just concatenate it using "format json" feature in sql. However, I do not have that level of control over the resulting json. The requirement is to make it look like the first result I posted.

Thoughts?

[JonBoy]

Have you considered using DATA-GEN with the YAJL generator? It has this kind of capability designed in.

[B.Hauser]

What exactly you want to get returned? A piece of JSON or do you want to decompose the JSON into a table, i.e. CustomerNumber, CustomerName, OrderNumber etc.
If you want to decompose the JSON data, you have to work with the JSON_TABLE Table Function.

The following SQL Statement decomposes your JSON Document into a table:

Select *
  from JSON_Table('{
                    "customer" : 123456,
                    "customerName" : "ACME Shipping",
                    "customerPhone" : 5555555555,
                    "orders": [{
                                "orderNumber" : "5555",
                                "orderTotal" : 123.45
                               }, {
                                "orderNumber" : "4444",
                                "orderTotal" : 321.00
                               }
                              ]
                     }',  '$'
                    Columns("customer"      Integer,
                            "customerName"  VarChar(30),
                            "customerPhone" BigInt,
                            Nested '$.orders[*]'
                                Columns("orderNumber" VarChar(10),
                                        "orderTotal"  Dec(11, 2)))) x;

Result:

[B.Hauser]

For retrieving only specific JSON Data you may also have a look at the JSON_VALUE Function.

[JonBoy]

What exactly you want to get returned? A piece of JSON or do you want to decompose the JSON into a table, i.e. CustomerNumber, CustomerName, OrderNumber etc.
If you want to decompose the JSON data, you have to work with the JSON_TABLE Table Function.

The following SQL Statement decomposes your JSON Document into a table:

Select *
from JSON_Table('{
"customer" : 123456,
"customerName" : "ACME Shipping",
"customerPhone" : 5555555555,
"orders": [{
"orderNumber" : "5555",
"orderTotal" : 123.45
}, {
"orderNumber" : "4444",
"orderTotal" : 321.00
}
]
}', '$'
Columns("customer" Integer,
"customerName" VarChar(30),
"customerPhone" BigInt,
Nested '$.orders[*]'
Columns("orderNumber" VarChar(10),
"orderTotal" Dec(11, 2)))) x;

Result:

The OP said they wanted to "generate json" Birgitta. Not decompose it.