Skipping JSON_ARRAYAGG array when one of the value is not available-In Oracle

Question

I am working in JSON_ARRAYAGG in Oracle .I have a scenario that Json array have 2 attributes. One attribute is hardcode value another one fetches value from outer query .

I want to skip whole Json_array to be skipped when the second attribute returns null value. Though I am giving "absent on null returning blob".but still I cant able to achieve that. Any possibilities to arrest that in Oracle.

My query:

'field' VALUE
    SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
            '@type' VALUE 'idtype'
            '@value' VALUE tbl.value absent on null returning blob)absent on null returning blob)
            from (select * from table tbl)

Expected output :

Null

Actual output:

  "field": [
    {
      "@type": "idtype"
    }
  ],

Add a where condition to the subquery: where tbl.value is not null. See db<>fiddle — astentx
– astentx, Commented Dec 6, 2022 at 9:36

Barbaros Özhan · Accepted Answer · 2022-12-06 11:03:18Z

You can filter out any NULL values with a WHERE filter and use ABSENT ON NULL inside the JSON_ARRAYAGG and the outer JSON_OBJECT (which you do not show in the question but is implied by the key-value pair):

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

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT CAST(NULL AS NUMBER) FROM DUAL;

Outputs:

JSON
{}

Then if you insert a non-NULL row:

INSERT INTO table_name (value) VALUES (1);

Then the output is:

JSON
{"field":[{"@type":"idtype","@value":1}]}

fiddle

Collectives™ on Stack Overflow

Skipping JSON_ARRAYAGG array when one of the value is not available-In Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related