1

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?

Improve this question

3 Answers 3

Reset to default
2

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;
Improve this answer
4
  • So you just copy the content of the edit of Lukasz in a new answer and mark it as solution?
    – Anho
    Commented Oct 22, 2020 at 7:16
  • Dear @Anho, thank you so much for your very considerate comment. 1) it is not a copy of Lukasz answer, please review our answers very carefully. 2) I wrote my answer first, please look at timestamps very carefully. Lukasz was so kind as to augment my suggestions with other options. 3) Please don't assume evil before you are very sure.
    – Lukas Eder
    Commented Oct 22, 2020 at 8:58
  • You're right. Sorry. At time of posting, your comment was second and I wrongly assumed posts are ordered by creation time. I really should have checked further.
    – Anho
    Commented Oct 26, 2020 at 11:20
  • @Anho: No worries. Btw, it's a recommended practice on Stack Overflow to answer one's own questions in order to share knowledge. I already knew the answer when I posted the "question"...
    – Lukas Eder
    Commented Oct 26, 2020 at 17:11
1

I would move the scalar subquery from SELECT list to LATERAL part which will be an exact equivalent. It should also satify the parser:

SELECT JSON_OBJECT(KEY 'x' VALUE my_scalar)
FROM sysibm.dual, LATERAL(SELECT 1 AS my_scalar FROM sysibm.dual);

db<>fiddle demo

EDIT:

Hack with function:

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

Here MAX because it accepts a single argument and does not change data type(same as COALESCE/DECODE).

Improve this answer
4
  • 1
    Interesting idea. In my case (jOOQ support for JSON_OBJECT), it won't be practical, because there are too many caveats when moving an expression from SELECT (or ORDER BY, WHERE, etc.) to the FROM clause. For example, the semantics of the asterisk * would change, also the alias of the lateral derived table and the synthetic column could produce conflicts. But it's great to see that this workaround, too, works - meaning that this is really just a parser bug.
    – Lukas Eder
    Commented Oct 14, 2020 at 17:51
  • @LukasEder Yes, then your approach with providing expression(function call) is a better one. I could propose MAX(not the aggregate one but rather GREATEST equivalent).
    – Lukasz Szozda
    Commented Oct 14, 2020 at 18:05
  • 1
    Interesting, I didn't know that function. Might be interesting to explore in general!
    – Lukas Eder
    Commented Oct 14, 2020 at 18:14
  • @LukasEder It seems MAX/MIN has its own quirks due to sharing the same name with agg function. db<>fiddle demo. Staying with COALESCE seems to be the safest option.
    – Lukasz Szozda
    Commented Oct 14, 2020 at 18:30
0

Another solution is to define the json object as a varchar string so that it can be displayed on screen.

SELECT varchar(
  JSON_OBJECT(KEY 'x' VALUE MAX((SELECT 1 FROM sysibm.sysdummy1)))            
             ,100) 
FROM sysibm.sysdummy1
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.