Re: db2 sql json_table and multiple rows

[Jay Vaughn <jeffersonvaughn@xxxxxxxxx>]

actually no - that introduces the array syntax to the json which is not
wanted

Jay

On Mon, Nov 27, 2023 at 11:47 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

> think I got it...
>
>
> values *json_object*(
> key 'test'
> *value*
>  (select *json_arrayagg*(
>           *json_object*(
>           *trim*(sys_tname)
>           *value* *json_object*(
>           'tableName'    *value* *trim*
> (sys_tname)
>                            )
>                       )
>                       )
>  from qsys2.systables
>  where sys_dname = 'QGPL')
>    );
>
> On Mon, Nov 27, 2023 at 11:25 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
> wrote:
>
> > So when I do the below, I get a list of objects as expected...
> >
> >
> > *-- values json_object(*
> > *-- key 'test'*
> > *-- value    * (select *json_object*(
> >           *trim*(sys_tname)
> >           *value* *json_object*(
> >           'tableName'    *value* *trim*
> > (sys_tname)
> >                            )
> >                       )
> >  from qsys2.systables
> >  where sys_dname = 'QGPL');
> > *--   ); *
> >
> > But when I remove the comments, because I want the list of objects within
> > another object, I get the following error...
> >
> > sqlcode -811
> > THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET
> > CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE
> > RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE
> >
> > So what is the correct syntax?
> >
> > tia
> > Jay