sqljsondb2

How to use a scalar subquery in Db2's JSON_OBJECT's VALUE clause?

I'm trying to create a JSON_OBJECT() from a scalar subquery like this in Db2 LUW v11.5.4.0:

SELECT
  JSON_OBJECT(KEY 'x' VALUE (SELECT 1 FROM sysibm.dual))
FROM sysibm.dual;

This produces

An unexpected token ")" was found following "1 from sysibm.dual)". Expected tokens may include: "<interval_qualifier>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14

A similar query works in Oracle, and I don't see, from the documentation, what I'm doing wrong in Db2. How can I construct a JSON_OBJECT() from a scalar subquery?

Solution

  • These hacks, and possibly others, seem to work:

    SELECT
  JSON_OBJECT(KEY 'x' VALUE COALESCE(NULL, (SELECT 1 FROM sysibm.dual)))
FROM sysibm.dual;

SELECT
  JSON_OBJECT(KEY 'x' VALUE DECODE(1, 1, (SELECT 1 FROM sysibm.dual)))
FROM sysibm.dual;