Easiest way to create nested json

[fvestjens]

I have an order table with products and order lines.

Select Orders.Id, Customer.Id 
from Orders
join Customer on Customer.Id = Orders.CustomerId
where Order.Id = 1 
for json auto

This could return

[{"Order": {"Id":1,
            "Customer": [{"Id": "10"}]
           }
}]

so far so good.

If I want to extend the json with the OrderLines like

Select Orders.Id, Customer.Id, OrderLines.Id
from Orders
join Customer on Customer.Id = Orders.CustomerId
join OrderLines on OrderLines.OrderId = Orders.Id
where Order.Id = 1 
for json auto

I get this result

[{"Order": {"Id":1,
            "Customer": [{"Id": "10",
                         "OrderLines": [{"Id":1},{"Id":2"}]
                       }]
           }
}]

but I want

{"Order": [{"Id":1,
            "Customer": {"Id": "10"},
            "OrderLines": [{"Id":1},{"Id":2"}]
           }] 

}

Is there an easy way to do this? Or is the only way to do this using string concatenation?

I know MS SQL has a solution for this. There it would look like:

select Orders.Id
      ,Customer.Id as 'Customer.Id'
      ,(select OrderLines.Id 
        from OrderLines 
        where OrderLines.OrderId = Orders.Id
        for json path) as 'OrderLines'
from Orders
join Customer on Customer.Id = Orders.CustomerId
join OrderLines on OrderLines.OrderId = Orders.Id
where Order.Id = 1 
for json path, root('Order')

Does anyone know whether SAP is working on something like this?

[VolkerBarth]

Does anyone know whether SAP is working on something like this?

Yes, I would also like to know whether there are plans to improve the JSON support in SQL Anywhere 17. IMHO, exporting and importing with JSON seems way more restricted than with XML...

[fvestjens]

Thanks for all the input. I ended op using:

Select Orders.Id, '<customer>', '<orderlines>'
into json_Order
from Orders
for json raw;

set json_Order = replace(json_Order,'"<customer>"',jsonCustomer(CustomerId));
set json_Order = replace(json_Order,'"<orderlines>"',jsonOrderLines(CustomerId));

Where jsonCustomer/jsonOrderLines are stored functions creating the json for a Customer/OrderLines

[VolkerBarth]

That's apparently way easier to understand than my earlier attempts. 🙂

[fvestjens]

I've found another way to achieve this but I'm trying to find out if it could be more compact then what it is now. And how to integrate it in a select statement.

Maybe somebody got an idea on that.

create variable row_Customer ROW(AddressId integer, Name varchar(64), City varchar(64));

create variable row_Product ROW(ProductId integer, Description varchar(64));

create variable array_Products array of row_Product%TYPE;

create variable rowOrder row(Id integer, Customer row_Customer%TYPE, Products array_Products%TYPE);

set row_Customer = row(850,'InforIT BV','Oss');

set row_Product = row(1,'Milk');  set array_Products[[1]] = row_Product;
set row_Product = row(2,'Water'); set array_Products[[2]] = row_Product;
set row_Product = row(3,'Soup');  set array_Products[[4]] = row_Product;

set rowOrder = ROW(1,row_Customer,array_Products);

select rowOrder as "Order"
from dummy
for json raw

[VolkerBarth]

A different attempt would be to use sub queries in the select list for nested tables and generate those FOR JSOW RAW and use a final FOR JSON AUTO to add the elements accordingly, such as

select Orders.ID, Orders.OrderDate,
   (select Customers.ID, Customers.Surname
    from GROUPO.Customers 
    where Customers.ID = Orders.CustomerID
    for json raw) as Customers,
   (select OrderItems.ProductID
    from GROUPO.SalesOrderItems OrderItems
    where OrderItems.ID = Orders.ID
    for json raw) as OrderItems
from GROUPO.SalesOrders Orders
where Orders.ID in (2001, 2002)
order by 1
for json auto;

returns

[{"Orders":{"ID":2001,"OrderDate":"2000-03-16",
"Customers":"[{\\"ID\\":101,\\"Surname\\":\\"Devlin\\"}]",
"OrderItems":"[{\\"ProductID\\":300},{\\"ProductID\\":301},{\\"ProductID\\":302}]"}},
{"Orders":{"ID":2002,"OrderDate":"2000-03-20",
"Customers":"[{\\"ID\\":102,\\"Surname\\":\\"Reiser\\"}]",
"OrderItems":"[{\\"ProductID\\":400},{\\"ProductID\\":401}]"}}]

Note, the result set must be manipulated afterwards to replace the masked double quotes, and there are still some issues (e.g. "OrderItems" ought to be an array of ProductIDs here, not an object), but as stated, that might be another approach.

Alternatively, one might use the sub queries via FOR JSON AUTO and use a STRING function to concatenate the column values similar to that FAQ...

Just my two cents, anyway, I still hope there are better approaches.

[VolkerBarth]

Hm, it seems FOR JSON AUTO expects joins to be "hierarchical", i.e. each table's elements are nested within the elements of the table "before", even when the third table is only dependent on the first and not on the second...

Here's an attempt via FOR JSOn EXPLICIT – note, I have rarely used that, and it's certainly not easy (because that's what you had asked for...), and it also adds undesired arrays. But it may give you a start... (FWIW, this is tested within the SA17.0.10.6230 demo database).

select
   1 as tag,
   null as parent,
   Orders.ID as [!1!ID],
   Orders.OrderDate as [!1!OrderDate],
   null as [Customer!2!CustomerID],
   null as [!2!Surname],
   null as [OrderItems!3!ID],
   null as [!3!ProductID]
from GROUPO.SalesOrders Orders
where Orders.ID = 2001
union all
select
   2 as tag,
   1 as parent,
   Orders.ID,
   Orders.OrderDate,
   Customers.ID,
   Customers.Surname,
   null,
   null
from GROUPO.SalesOrders Orders
   key join GROUPO.Customers 
where Orders.ID = 2001
union all
select
   3 as tag,
   1 as parent, -- note: same parent as tag 2
   Orders.ID,
   Orders.OrderDate,
   null,
   null,
   OrderItems.ID,
   OrderItems.ProductID
from GROUPO.SalesOrders Orders
   key join GROUPO.SalesOrderItems OrderItems
where Orders.ID = 2001
order by 1
for json explicit

returns

[{"ID":2001,"OrderDate":"2000-03-16",
    "Customer":[{"CustomerID":101,"Surname":"Devlin"}],
    "OrderItems":[{"ID":2001,"ProductID":300},{"ID":2001,"ProductID":301}, "ID":2001,"ProductID":302}]
}]

[fvestjens]

FOR JSON EXPLICIT is not the way forward as far as I'm concerned for creating json data.

The json I need to generate consists of multiple objects and for every object an union all needs to be added. and for every union all you need to define a set of NULL columns.

Then this solution is not very friendly at all. And errors can easily occur with a mismatch in NULL columns

[VolkerBarth]

Well, as stated, I do not claim it to be easy or comprehensible, either...