|
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? |
|
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 That's apparently way easier to understand than my earlier attempts. :)
(25 Mar '21, 04:15)
Volker Barth
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
(25 Mar '21, 10:42)
Frank Vestjens
|
|
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}]
}]
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
(25 Jan '21, 06:44)
Frank Vestjens
Replies hidden
1
Well, as stated, I do not claim it to be easy or comprehensible, either...
(25 Jan '21, 07:04)
Volker Barth
|
|
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. |
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...