Re: db2 sql json_table and multiple rows

Show an example of what you do want...

Charles

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

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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.