JSON 関数は、 JSON  (JavaScript Object Notation) データを扱うための機能を提供します。このページでは、同じサンプルテーブル構造とデータセットを例に、すべてのJSON関数を1つずつ説明します。 

Sample Data For Examples

以下は、この例で使用するテーブルの構造です：

TABLE Customer (
    CustomerId integer PRIMARY KEY,
    CustomerName varchar(25),
    ContactName varchar(25),
    Address varchar(50),
    City varchar(25),
    PostalCode varchar(25),
    Country varchar(25)
);

ANDはサンプルデータです：

To view the full table, click the expand button in its top right corner


CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

87

Wartian Herkku

Pirkko Koskitalo

Torikatu 38

Oulu

90110

Finland

88

Wellington Importadora

Paula Parente

Rua do Mercado, 12

Resende

08737-363

Brazil

89

White Clover Markets

Karl Jablonski

305 - 14th Ave. S. Suite 3B

Seattle

98128

USA

JSONTOXML

この関数は  JSON ドキュメントを XML ドキュメントに変換します。

Syntax

JSONTOXML(rootElementName, json)
  • rootElementNameは現在の要素名です。これは、  JSON構造がトラバースされ、 が文字列であるため、オブジェクト値名になります；
  • jsonは {clob, blob} にあります；
  • 戻り値は XML です。

JSONブロブに対して適切なUTFエンコーディング（8、16LE、16BE、32LE、32BE）が検出されます。他のエンコーディングが使用される場合は、to_chars関数を参照してください。
この関数の結果は常に整形式のXML文書で、XMLへのマッピングには以下のルールが使用されます：

  • すべての要素名は有効なXML1.1の名前でなければなりません。無効な名前はSQLXML仕様に従って完全にエスケープされます；
  • 各オブジェクトまたはプリミティブ値は、現在の名前を持つ要素で囲まれます；
  • 配列の値がルートでない限り、追加要素で囲まれることはありません；
  • NULL値は、属性xsi:nil="true"、空の要素で表現されます；
  • Booleanと数値の値要素は、それぞれ属性xsi:type、booleanとdecimalに設定されます。

Examples

JSONTOXML('person', x) のサンプル JSON から XML への変換

JSON

{"firstName" : "John" , "children" : [ "Randy", "Judy" ]}

XML

<?xml version="1.0"?>
<person>
<firstName>John</firstName>
<children>Randy</children>
<children>Judy<children>
</person>

JSONTOXML('person', x) のサンプル JSON から XML への変換 (ルート配列付き)

JSON

[{"firstName" : "George" }, { "firstName" : "Jerry" }]

XML

<?xml version="1.0" ?>
    <person>
        <person>
            <firstName>George</firstName>
        </person>
        <person>
         <firstName>Jerry</firstName>
     </person>
</person>

この例では、XMLを整形式に保つために "person "ラッパー要素が追加されていることに注意してください。

JSONTOXML('root', x) で名前が無効な場合のサンプル JSON to XML

JSON

{"/invalid" : "abc" }  

XML

<?xml version="1.0" ?> <root> <_x002F_invalid>abc</_x002F_invalid> </root>

Prior releases defaulted incorrectly to using uXXXX escaping rather than xXXXX. Please see the system property if you need to rely on that behaviour.

JsonPath Support

JsonPath のサポートはJayway JsonPathで提供されています。1ベースのインデックスではなく、0ベースのインデックスを使用していることに注意して、さまざまなパス表現に対して期待されるリターンをチェックしてください。例えば、行のJsonPath式が配列を提供することを期待されている場合、それはあなたが望む配列であり、配列や不定パス式によって自動的に返される配列ではないことを確認してください。

パス名に'.'などの予約文字が使用されている状況に遭遇した場合は、$['.key']のように、任意のキーが使用できるように、括弧付きのJsonPath表記を使用してください。

JSONPATHVALUE

この関数は、1つのJSON値を文字列として取り出します。

Syntax

JSONPATHVALUE(value, path [, nullLeafOnMissing])
  • valueは clob JSON ドキュメントです；
  • pathは JsonPath 文字列です；
  • nullLeafOnMissingはブール値です；
  • 戻り値は、結果のJSONの文字列値です。

nullLeafOnMissingがFALSE (デフォルト) の場合、欠落しているリーフを評価するパスは例外をスローします。nullLeafOnMissingがTRUEの場合、NULL 値が返されます。

値が不定パス式によって生成された配列の場合、最初の値のみが返されます。

Examples

1. このサンプルコードはnullを返します：

jsonPathValue('{"key":"value"}', '$.missing', true)

2. このサンプルコードはvalue1を返します：

SELECT jsonPathValue('[{"key":"value1"}, {"key":"value2"}]', '$..key');;

JSONPATHVALUE is available since v4.0.8 

JSONQUERY

この関数は、JsonPath式をJSONドキュメントに対して評価し、JSON結果を返します。

Syntax

JSONQUERY(value, path [, nullLeafOnMissing])
  • valueは clob JSON ドキュメントです；
  • pathは JsonPath 文字列です；
  • nullLeafOnMissingはブール値です；
  • 戻り値はJSON値です。

nullLeafOnMissingがFALSE (デフォルト) の場合、欠落しているリーフを評価するパスは例外をスローします。nullLeafOnMissingがTRUEの場合、NULL 値が返されます。

Example

このサンプルコードは["value1","value2"]を返します：

SELECT JSONQUERY('{"key":"value"}','$.missing', true);;

JSONQUERY is available since v4.0.8 

JSONTABLE

JSONTABLE関数はJsonPathを使用して表形式の出力を生成します。これは暗黙的にネストされたテーブルであり、直前のFROM Clause項目と相関があるかもしれません。

Usage

JSONTABLE(value, path [, nullLeafOnMissing] COLUMNS <COLUMN>, ... ) AS name
COLUMN := name (FOR ORDINALITY | (datatype [PATH string]))

Parameters

  • valueは有効なJSONドキュメントを含むCLOBです；

  • nullLeafOnMissingがFALSE（DEFAULT） の場合、欠落しているリーフを評価するパスは例外をスローします。nullLeafOnMissingが TRUE の場合、NULL 値が返されます；

  • パス文字列は有効なJsonPathでなければなりません。配列値が返された場合、NULLでない各要素が行の生成に使用されます。そうでない場合は、単一の行を作成するために単一の非NULL項目が使用されます；

  • FOR ORDINALITYカラムはintegerとして型付けされ、その値として1ベースのアイテム番号を返します；

  • 各非順位の列は、型を指定し、オプションでPATHを指定します；

  • PATHが指定されない場合、パスはカラム名から生成されます: @['name'] -は名前に一致するオブジェクトキー値を探します。PATHが指定された場合、@で始まらなければなりません。これは、パスが現在の行コンテキスト項目からの相対パスで処理されることを意味します。

Syntax Rules

  • カラム名に重複があってはなりません；

  • 配列タイプは現在サポートされていません。

Examples

  1. パッシングを使用すると、1行[1]を返します：

SELECT * FROM JSONTABLE('{"a": {"id":1}}}', '$.a' COLUMNS id integer) x

  1. ネストされたテーブルとして：

SELECT x.* FROM t, JSONTABLE(t.doc, '$.x.y' COLUMNS first string, second FOR ORDINALITY) x

  1. もっと複雑な経路で：

SELECT x.* FROM JSONTABLE('[{"firstName": "John", "lastName": "Wayne", "children": []}, {"firstName": "John", "lastName": "Adams", "children":["Sue","Bob"]}]', '$.*' COLUMNS familyName string path '@.lastName', children integer path '@.children.length()' ) x

JSONTABLE is available since v4.0.8

JSONARRAY

この関数は JSON 配列を返します。

Syntax

JSONARRAY(value...)
  • 値は、JSON値に変換可能な任意のオブジェクトにすることができます；
  • 戻り値はCLOB 有効なJSONであるとマークされたものです；
  • NULL値はNULLリテラルとして結果に含まれます。

Examples

混合値の例

JSON

JSONARRAY('a"b', 1, NULL, FALSE, {d'2010-11-21'})

XML

["a\"b",1,null,false,"2010-11-21"]

テーブルでのJSONARRAY の使用

SELECT JSONARRAY(CustomerId, CustomerName)
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
[88,"Wellington Importadora"]
[89,"White Clover Markets"]
*/

JSONOBJECT

この関数は JSON オブジェクトを返します。

Syntax

JSONOBJECT(value [as name] ...)
  • 値は、JSON  値に変換可能な任意のオブジェクト です。戻り値はclob有効なJSONであるとマークされたものです；
  • NULL値はNULLリテラルとして結果に含まれます；
  • 名前が提供されず、式がカラム参照の場合、カラム名が使用されます。それ以外の場合は、exprNが使用されます。Nは、JSONARRAY表現の値の1ベースのインデックスです。

Examples

混合値の例

JSON

JSONOBJECT('a"b' AS val, 1, NULL as "null")

XML

{"val":"a\"b","expr2":1,"null":null}

テーブルでの JSONOBJECT の使用

SELECT JSONOBJECT(CustomerId, CustomerName)
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
{"CustomerId":88, "CustomerName":"Wellington Importadora"}
{"CustomerId":89, "CustomerName":"White Clover Markets"}
*/
SELECT JSONOBJECT(JSONOBJECT(CustomerId, CustomerName) AS Customer)
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
{"Customer":{"CustomerId":88, "CustomerName":"Wellington Importadora"}}
{"Customer":{"CustomerId":89, "CustomerName":"White Clover Markets"}}
*/
SELECT JSONOBJECT(JSONARRAY(CustomerId, CustomerName) AS Customer)
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
{"Customer":[88, "Wellington Importadora"]} {"Customer":[89, "White Clover Markets"]}
*/

JSONPARSE

この関数はバリデーションを行い、JSONの結果を返します。

Syntax

JSONPARSE(value, wellformed)
  • 値は、適切なJSONバイナリエンコーディング（UTF-8、UTF-16、またはUTF-32）を持つblob、またはCLOB。wellformedは、バリデーションがスキップされるべきであることを示すブール値です；
  • 戻り値はclob有効なJSONであるとマークされたものです；
  • どちらかの入力がNULLの場合、NULLが返されます。

Example

単純なリテラル値のJSON解析

jsonParse('{"Customer":{"CustomerId":88, "CustomerName":"Wellington Importadora"}}', true)

JSONARRAY_AGG

この関数は JSON 配列の結果をCLOB (NULL 値を含む) として作成します。これはJSONARRAYと似ていますが、その内容を1つのオブジェクトに集約します。

SELECT JSONARRAY_AGG(JSONOBJECT(CustomerId, CustomerName))
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
[{"CustomerId":88, "CustomerName":"Wellington Importadora"},
{"CustomerId":89, "CustomerName":"White Clover Markets"}]
*/

配列をラップする方法は他にもあります：

SELECT JSONOBJECT(JSONARRAY_AGG(JSONOBJECT(CustomerId AS id, CustomerName AS name)) AS Customer)
FROM Customer c
WHERE c.CustomerID >= 88;;
/* Outcome:
{"Customer":[{"id":89,"name":"Wellington Importadora"},{"id":100,"name":"White Clover Markets"}]}
*/

Conversion to JSON

値を適切なJSON ドキュメント形式に変換するには、仕様に準拠した簡単な変換が使用されます。ルールは以下のとおり：

  • NULL値はNULLリテラルとして含まれます；

  • JSONとしてパースされた値、またはJSON構築関数( JSONPARSE , JSONARRAY , JSONARRAY_AGG )から返された値は、JSON結果に直接追加されます；

  • ブール値はtrue / falseリテラルとして含まれます；

  • NUMERIC値は、デフォルトの文字列変換として含まれています。状況によっては、数値や+-無限大の結果が許可されている場合、無効なJSONが取得されることがあります；

  • 文字列値は、エスケープ/引用符で囲まれた形式で含まれます；

  • BINARY値はJSON値に暗黙的に変換されず、JSONに含める前に特定の値を必要とします；

  • その他の値はすべて、適切なエスケープ/引用符で囲まれた形で文字列に変換されます。