Oracle PLSQL -从JSON获取JSON

我会尽量短！我已经在PLSQL中有了JSON解析，但是客户端需要每个“RESULT”的JSON字符串，比如每个都相当于主表中的一个“RECORD”。问题是，我不能只检索结果字符串在一个简单的方式，而不使用INSTR，SUBSTR或类似的，至少我不知道如何在PLSQL。我知道如何在PowerShell中做，但客户端只能使用PLSQL。下面是JSON示例和我在PowerShell中的操作，这就是我想在PL/SQL上实现的
JSON结构API响应

{"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示例

#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
}

型
我尝试了很多命令，但远远没有得到结果。
我从表中解析JSON，CLOB列存储JSON字符串。
从API STRUCTURE我想实现的：

{ "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": {} }

型
我还尝试了JSON_QUERY，但它甚至没有达到我的预期效果

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

型

经过长时间的研究，阅读，学习和测试，我发现从JSON内部检索JSON的一部分，或者使用JSON_TABLE将原始JSON恢复为JSON的方法是使用以下列定义：

CLOB FORMAT JSON WITHOUT WRAPPER PATH

字符串
示例：

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;

型
它将在列“reg_json”上检索MAIN JSON中每个记录的JSON等效项，如给定示例中的以下结构：

{ "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": {}
}

型

Oracle PLSQL -从JSON获取JSON

1条答案

相关问题

热门标签

最新问答