Re: Help with building a nested JSON command

[Daniel Gross <daniel@xxxxxxxx>]

Hi Darryl,

> Am 12.08.2025 um 18:20 schrieb a4g atl <a4ginatl2@xxxxxxxxx>:

> Select json_object(
> key 'CMSNumber' value q100.kcms# -- value "CMSNumber"
> , key 'EnteredBy' value q100.kendt -- value "EnteredBy"
> , key 'Currency' value
> case when ccrcd = '' then 'USD'
> else ccrcd
> end
> -- this is the object within the object
> , 'ErrorUsers' value json_object (
> key 'User' value ke1usr absent on null
> , key 'User' value ke2usr absent on null
> , key 'User' value ke3usr absent on null
> , key 'User' value ke4usr absent on null
> , key 'User' value ke5usr absent on null

Your problem is, that you have the same key „User“ multiple times,

> )
>
> , key 'SalesmanNumber' value ksalno
> , key 'SalesmanName' value smnam
>
> from ....

If you want something like that, you would have to create an JSON array - AFAIK it is not allowed to use the same key in one object multiple times.

HTH
Daniel