Oracle PLSQL - Obtaining JSON from JSON

Question

I will try to be short! I already have the JSON PARSING in PLSQL, but the client need the JSON string of each "RESULT", such as each one is equivalent to a "RECORD" in the main table. The problem is that I can't retrieve only the result STRING in a simple way without using INSTR, SUBSTR or similar, at least I don't know how in PLSQL. I know how to do in PowerShell, but the client only works with PLSQL. Bellow the example JSON and how I do in PowerShell, that is what I want to achieve on PL/SQL

JSON STRUCTURE API RESPONSE

{"header1": "val_header1",
  "header2": "val_header2",
  "header3": "val_header3",
  "header4": "",
  "header5": "val_header5",
  "header6": [
    "val_header6"
  ],
  "_embedded": {
    "results": [
      { "col1": "value1",
        "col2": "value2",
        "col3": "value3",
        "col4": "value4",
        "col5": "value5",
        "col6": [
          {
            "col6a": "",
            "col6b": "value6b"
          }
        ],
        "col7a": {
          "col7a1": {
            "col7a1a": "value7a1a",
            "col7a1b": "value7a1b",
            "col7a1c": "value7a1c"
          },
          "col7b1": [
            {
              "col7b1a": "value7a1a-1",
              "col7b1b": "value7a1a-1",
              "col7b1c": "value7a1a-1"
            },
            {
              "col7b1a": "value7a1a-2",
              "col7b1b": "value7a1a-2",
              "col7b1c": "value7a1a-2"
            }
          ],
        },
        "_embedded": {}
      },
      { "col1": "value1",
        "col2": "value2",
        "col3": "value3",
        "col4": "value4",
        "col5": "value5",
        "col6": [
          {
            "col6a": "",
            "col6b": "value6b"
          }
        ],
        "col7a": {
          "col7a1": {
            "col7a1a": "value7a1a",
            "col7a1b": "value7a1b",
            "col7a1c": "value7a1c"
          },
          "col7b1": [
            {
              "col7b1a": "value7a1a-1",
              "col7b1b": "value7a1a-1",
              "col7b1c": "value7a1a-1"
            },
            {
              "col7b1a": "value7a1a-2",
              "col7b1b": "value7a1a-2",
              "col7b1c": "value7a1a-2"
            }
          ],
        },
        "_embedded": {}
      },
      { "col1": "value1",
        "col2": "value2",
        "col3": "value3",
        "col4": "value4",
        "col5": "value5",
        "col6": [
          {
            "col6a": "",
            "col6b": "value6b"
          }
        ],
        "col7a": {
          "col7a1": {
            "col7a1a": "value7a1a",
            "col7a1b": "value7a1b",
            "col7a1c": "value7a1c"
          },
          "col7b1": [
            {
              "col7b1a": "value7a1a-1",
              "col7b1b": "value7a1a-1",
              "col7b1c": "value7a1a-1"
            },
            {
              "col7b1a": "value7a1a-2",
              "col7b1b": "value7a1a-2",
              "col7b1c": "value7a1a-2"
            }
          ],
        },
        "_embedded": {}
      }
    ]
  }
}

POWERSHELL EXAMPLE

#Request API
$get_list = Invoke-WebRequest -Uri $get_url

#Convert JSON in powershell-object
$json_object = $get_list | ConvertFrom-Json

#for loop for each result value existent
foreach ($json_obj_detail in $json_object._embedded.results)
{
    #Retrieve RESULT as JSON STRING
    $obj_det_json = $json_obj_detail | ConvertTo-Json -Depth 100 -Compress
}

I tried a lot of command, but far to get the results.

I'm parsing the JSON from table with CLOB column where is storage the JSON STRING.

From the API STRUCTURE I want to achieve:

{ "col1": "value1", "col2": "value2", "col3": "value3", "col4": "value4", "col5": "value5", "col6": [ { "col6a": "", "col6b": "value6b" } ], "col7a": { "col7a1": { "col7a1a": "value7a1a", "col7a1b": "value7a1b", "col7a1c": "value7a1c" }, "col7b1": [ { "col7b1a": "value7a1a-1", "col7b1b": "value7a1a-1", "col7b1c": "value7a1a-1" }, { "col7b1a": "value7a1a-2", "col7b1b": "value7a1a-2", "col7b1c": "value7a1a-2" } ], }, "_embedded": {} }

I also tried JSON_QUERY, but got not even close to what I intended

SELECT JSON_QUERY(jsn.clob_json,'$._embedded.results')
FROM json_documents jsn

Answer 1

After a long research, reading, study and tests, I found out that the whay to retrieve a part of the JSON from inside of the JSON, or recover the original JSON as JSON using JSON_TABLE is using the follow column definition:

CLOB FORMAT JSON WITHOUT WRAPPER PATH

Exemple:

SELECT *
    FROM JSON_TABLE ( < JSON STRING (OR CLOB) >
                    ,'$._embedded.results[*]'
                         COLUMNS (reg_json      CLOB FORMAT JSON WITHOUT WRAPPER PATH '$'
                                 ,col1          VARCHAR2(4000 CHAR) PATH '$.col1'
                                 ,col2          VARCHAR2(4000 CHAR) PATH '$.col2'
                                 ,[...]
                                 ,col5          VARCHAR2(4000 CHAR) PATH '$.col5'
                                 ,[...]
                                ) ) j_tab;

It will retrieve on the column "reg_json" the JSON equivalent of each record inside the MAIN JSON as the following structure from the given example:

{ "col1": "value1",
  "col2": "value2",
  "col3": "value3",
  "col4": "value4",
  "col5": "value5",
  "col6": [
    {
      "col6a": "",
      "col6b": "value6b"
    }
  ],
  "col7a": {
    "col7a1": {
      "col7a1a": "value7a1a",
      "col7a1b": "value7a1b",
      "col7a1c": "value7a1c"
    },
    "col7b1": [
      {
        "col7b1a": "value7a1a-1",
        "col7b1b": "value7a1a-1",
        "col7b1c": "value7a1a-1"
      },
      {
        "col7b1a": "value7a1a-2",
        "col7b1b": "value7a1a-2",
        "col7b1c": "value7a1a-2"
      }
    ],
  },
  "_embedded": {}
}