Re: db2 sql json_table and multiple rows

well wouldn't you know... there is actually an objectAgg!!!

Here is what works and what I was looking for...
(can leave the comments in place! - note the objectAgg...)


*-- values json_object(*
*-- key 'test'*
*-- value * (select *json_objectAgg*(
*trim*(sys_tname)
*value* *json_object*(
'tableName' *value* *trim*
(sys_tname)
)
)
from qsys2.systables
where sys_dname = 'QGPL');
*-- ); *

*= objects embedded in an object...*

{"QEWLSRC":{"tableName":"QEWLSRC"},"QFMTSRC":{"tableName":"QFMTSRC"},"QMNUSRC":{"tableName":"QMNUSRC"},"QAOEFFVL":{"tableName":"QAOEFFVL"},"QCLSRC":{"tableName":"QCLSRC"},"QCMDSRC":{"tableName":"QCMDSRC"},"QDDSSRC":{"tableName":"QDDSSRC"},"QEWCKBDMAP":{"tableName":"QEWCKBDMAP"},"QEWCSCNFMT":{"tableName":"QEWCSCNFMT"},"QEWCSRC":{"tableName":"QEWCSRC"},"QPNLSRC":{"tableName":"QPNLSRC"},"QREXSRC":{"tableName":"QREXSRC"},"QSRVSRC":{"tableName":"QSRVSRC"},"QSYSCHGS":{"tableName":"QSYSCHGS"},"QTBLSRC":{"tableName":"QTBLSRC"},"QRJXSRC":{"tableName":"QRJXSRC"},"QTXTSRC":{"tableName":"QTXTSRC"},"QASUUSRPMT":{"tableName":"QASUUSRPMT"},"QAUOOPT":{"tableName":"QAUOOPT"},"QINVREC":{"tableName":"QINVREC"},"QSALEREC":{"tableName":"QSALEREC"},"QORDHDR":{"tableName":"QORDHDR"},"QORDDTL":{"tableName":"QORDDTL"},"QAAPFILE$":{"tableName":"QAAPFILE$"},"QAAPFILE#":{"tableName":"QAAPFILE#"},"QAAPFILE@
":{"tableName":"QAAPFILE@
"},"QRPGLESRC":{"tableName":"QRPGLESRC"},"QRPGSRC":{"tableName":"QRPGSRC"},"QCBLLESRC":{"tableName":"QCBLLESRC"},"QCBLSRC":{"tableName":"QCBLSRC"},"QLBLSRC":{"tableName":"QLBLSRC"},"QCSRC":{"tableName":"QCSRC"},"QCPPSRC":{"tableName":"QCPPSRC"},"AUDITREP":{"tableName":"AUDITREP"},"FB4TXTSRC":{"tableName":"FB4TXTSRC"}}

*thanks *

*Jay*


On Mon, Nov 27, 2023 at 11:52 AM Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

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.

--
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.