はじめに

Oracle Database 23c Free - Developer ReleaseでJSON_VALUEまたはJSON_QUERYで述語を含むJSONパス式がサポートされるようになったので、試してみました。

Oracle Database 23c Free - Developer Releaseのインストール

こちらを参考にインストールします。

Setting Oracle Database Free Environment Variablesまで実行できたら完了です。

データベースへの接続

SQL*Plusからsysdbaとしてコンテナ・データベースに接続します。

[oracle@instance-20230605-1511-db23c ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Fri Jun 9 02:57:37 2023
Version 23.2.0.0.0

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0

ALTER SESSION SET CONTAINER文でPDB（FREEPDB1）に接続します。

SQL> ALTER SESSION SET CONTAINER=FREEPDB1;

Session altered.

SQL>

サンプルデータのINSERT

SQL*PlusからこちらのSQLを実行し、サンプルテーブルを作成します。

次にこちらのSQLを実行し、サンプルデータをINSERTします。

上記のSQLを実行するとj_purchaseorderというテーブルに以下のJSONデータが登録されます。

{
   "PONumber":1600,
   "Reference":"ABULL-20140421",
   "Requestor":"Alexis Bull",
   "User":"ABULL",
   "CostCenter":"A50",
   "ShippingInstructions":{
      "name":"Alexis Bull",
      "Address":{
         "street":"200 Sporting Green",
         "city":"South San Francisco",
         "state":"CA",
         "zipCode":99236,
         "country":"United States of America"
      },
      "Phone":[
         {
            "type":"Office",
            "number":"909-555-7307"
         },
         {
            "type":"Mobile",
            "number":"415-555-1234"
         }
      ]
   },
   "Special Instructions":null,
   "AllowPartialShipment":true,
   "LineItems":[
      {
         "ItemNumber":1,
         "Part":{
            "Description":"One Magic Christmas",
            "UnitPrice":19.95,
            "UPCCode":13131092899
         },
         "Quantity":9.0
      },
      {
         "ItemNumber":2,
         "Part":{
            "Description":"Lethal Weapon",
            "UnitPrice":19.95,
            "UPCCode":85391628927
         },
         "Quantity":5.0
      }
   ]
},
{
   "PONumber":672,
   "Reference":"SBELL-20141017",
   "Requestor":"Sarah Bell",
   "User":"SBELL",
   "CostCenter":"A50",
   "ShippingInstructions":{
      "name":"Sarah Bell",
      "Address":{
         "street":"200 Sporting Green",
         "city":"South San Francisco",
         "state":"CA",
         "zipCode":99236,
         "country":"United States of America"
      },
      "Phone":"983-555-6509"
   },
   "Special Instructions":"Courier",
   "LineItems":[
      {
         "ItemNumber":1,
         "Part":{
            "Description":"Making the Grade",
            "UnitPrice":20,
            "UPCCode":27616867759
         },
         "Quantity":8.0
      },
      {
         "ItemNumber":2,
         "Part":{
            "Description":"Nixon",
            "UnitPrice":19.95,
            "UPCCode":717951002396
         },
         "Quantity":5
      },
      {
         "ItemNumber":3,
         "Part":{
            "Description":"Eric Clapton: Best Of 1981-1999",
            "UnitPrice":19.95,
            "UPCCode":75993851120
         },
         "Quantity":5.0
      }
   ]
}

JSON_VALUEの実行

以下のSQLを実行します。

SQL> SELECT json_value(po_document, '$?(@.User=="ABULL").PONumber') AS PONumber FROM j_purchaseorder;

PONUMBER
--------------------------------------------------------------------------------
1600

Userの値がABULLであるPONumberの値がSELECTされ、指定した述語が正しく動作していることが確認できます。

JSON_QUERYの実行

次にJSON_QUERYを実行します。以下のSQLを実行します。

SQL> SELECT json_query(po_document, '$.ShippingInstructions?(@.name == "Alexis Bull")') AS ShippingInstructions FROM j_purchaseorder;

SHIPPINGINSTRUCTIONS
--------------------------------------------------------------------------------
{"name":"Alexis Bull","Address":{"street":"200 Sporting Green","city":"South San Francisco","state":"CA","zipCode":99236,"country":"United States of America"},"Phone":[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]}

nameの値がAlexis BullであるShippingInstructionsの値がSELECTされ、指定した述語が正しく動作していることが確認できます。

まとめ

試した結果、JSON_VALUE,JSON_QUERYのパス式で述語が記述できることが確認できました。JSON データのクエリに JSON パス式をより広範囲に適用することで開発者の生産性が向上するような気がします！

Oracle Database 23c Free - Developer ReleaseでJSON_VALUE,JSON_QUERYで述語を含むJSONパス式を試す