By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (value) AS
SELECT CAST(NULL AS NUMBER) FROM DUAL;
1 rows affected
SELECT JSON_OBJECT(
'field' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
'@type' VALUE 'idtype',
'@value' VALUE value
RETURNING BLOB
)
ABSENT ON NULL
RETURNING BLOB
)
ABSENT ON NULL
RETURNING BLOB
) AS json
FROM table_name
WHERE value IS NOT NULL
JSON |
---|
{} |
INSERT INTO table_name (value) VALUES (1);
1 rows affected
SELECT JSON_OBJECT(
'field' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
'@type' VALUE 'idtype',
'@value' VALUE value
)
ABSENT ON NULL
)
ABSENT ON NULL
RETURNING BLOB
) AS json
FROM table_name
WHERE value IS NOT NULL
JSON |
---|
{"field":[{"@type":"idtype","@value":1}]} |