I have a JSON like this, stored in a CLOB column:

{
    "code": 1,
    "required": [
        {
            "field": "R1",
            "value": 1
        },
        {
            "field": "R2",
            "value": 2
        }
    ],
    "optional": [
        {
            "field": "O1",
            "value": 1
        },
        {
            "field": "O2",
            "value": 2
        },
        {
            "field": "O3",
            "value": 3
        }
    ]
}

I want a SQL to produce a result with both required and optional values, like this:

FIELD | VALUE | REQUIRED
R1    |     1 |      YES
R2    |     2 |      YES
O1    |     1 |       NO
O2    |     2 |       NO
O3    |     3 |       NO

I can easily do it with an UNION ALL, like this:

SELECT FIELD,
       VALUE,
       'YES' AS REQUIRED
  FROM JSON_TABLE (:JSON, '$.required[*]' COLUMNS
                   FIELD PATH '$.field',
                   VALUE PATH '$.value')
UNION ALL
SELECT FIELD,
       VALUE,
       'NO' AS REQUIRED
  FROM JSON_TABLE (:JSON, '$.optional[*]' COLUMNS
                   FIELD PATH '$.field',
                   VALUE PATH '$.value')

But this JSON file is very large, so with UNION ALL it has performance issues.

How to make an SQL that produces the same results without using UNION ALL? I think this can improve performance.

I tried using the “|” operator (like JSON_TABLE (:JSON, '$.(required|optional)[*]'...) but it not works.