Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

JSON_TABLE Extract lists from different nodes

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.

Tags:

2

Answers


  1. 0

    But this JSON file is very large, so with UNION ALL it has performance issues.
    Serching JSON in RDB it is always performance issues, so be careful when u use json in RDB.

    So I’m not sure any other way will be much faster. Just normalize this data by extracting from json. It will be slow, but once. And then work with normalized data.

    Json in RDB is lazy way to store some data structure that used as is. And there some methods to extract data but all slow and created to some edge situation like extract it to store better in future 🙂

    You can try to use static view but i’m pretty sure it is not a good way for production.

    Login or Signup to reply.
  2. 0

    It’s possible to use multiple NESTED PATH expressions:

    with json_doc AS
(SELECT
'{
  "code": 1,
  "required": [
    {
      "field": "R1",
      "value": 1
    },
    {
      "field": "R2",
      "value": 2
    }
  ],
  "optional": [
    {
      "field": "O1",
      "value": 1
    },
    {
      "field": "O2",
      "value": 2
    },
    {
      "field": "O3",
      "value": 3
    }
  ]
}' AS json_data FROM dual
)
SELECT
  CASE WHEN jt.field_required IS NULL THEN jt.field_optional ELSE jt.field_required END AS field ,
  CASE WHEN jt.field_required IS NULL THEN jt.value_optional ELSE jt.value_required END AS value ,
  CASE WHEN jt.field_required IS NULL THEN 'NO' ELSE 'YES' END AS required 
from json_doc j,
     JSON_TABLE (json_data, '$' COLUMNS
                    (code PATH '$.code',
                       NESTED PATH '$.optional[*]'
                         COLUMNS (
                           field_optional PATH '$.field',
                           value_optional PATH '$.value'
                         ),                   
                       NESTED PATH '$.required[*]'
                         COLUMNS (
                           field_required PATH '$.field',
                           value_required PATH '$.value'
                         )                    
             
                    )
                )  jt;

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search