JSON 関数と演算子¶

JSONへのキャスト¶

BOOLEAN、TINYINT、SMALLINT、INTEGER、BIGINT、REAL、DOUBLE、またはVARCHARからのキャストがサポートされています。ARRAY、MAP、またはROWからのキャストは、配列の要素型がサポートされている型のいずれかである場合、またはマップのキー型がVARCHARであり、マップの値型がサポートされている型のいずれかである場合、または行のすべてのフィールド型がサポートされている型のいずれかである場合にサポートされています。キャストの動作を以下の例で示します。
SELECT CAST(NULL AS JSON); -- NULL
SELECT CAST(1 AS JSON); -- JSON '1'
SELECT CAST(9223372036854775807 AS JSON); -- JSON '9223372036854775807'
SELECT CAST('abc' AS JSON); -- JSON '"abc"'
SELECT CAST(true AS JSON); -- JSON 'true'
SELECT CAST(1.234 AS JSON); -- JSON '1.234'
SELECT CAST(ARRAY[1, 23, 456] AS JSON); -- JSON '[1,23,456]'
SELECT CAST(ARRAY[1, NULL, 456] AS JSON); -- JSON '[1,null,456]'
SELECT CAST(ARRAY[ARRAY[1, 23], ARRAY[456]] AS JSON); -- JSON '[[1,23],[456]]'
SELECT CAST(MAP_FROM_ENTRIES(ARRAY[('k1', 1), ('k2', 23), ('k3', 456)]) AS JSON); -- JSON '{"k1":1,"k2":23,"k3":456}'
SELECT CAST(CAST(ROW(123, 'abc', true) AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)) AS JSON); -- JSON '[123,"abc",true]'

注

NULLからJSONへのキャストは単純ではありません。スタンドアロンのNULLからのキャストは、JSON 'null'の代わりにSQLのNULLを生成します。ただし、NULLを含む配列またはマップからキャストする場合、生成されたJSONにはnullが含まれます。

注

ROWからJSONにキャストする場合、結果はJSONオブジェクトではなくJSON配列になります。これは、SQLでは行の名前よりも位置が重要であるためです。

JSONからのキャスト¶

BOOLEAN、TINYINT、SMALLINT、INTEGER、BIGINT、REAL、DOUBLE、またはVARCHARへのキャストがサポートされています。ARRAYおよびMAPへのキャストは、配列の要素型がサポートされている型のいずれかである場合、またはマップのキー型がVARCHARであり、マップの値型がサポートされている型のいずれかである場合にサポートされています。キャストの動作を以下の例で示します。
SELECT CAST(JSON 'null' AS VARCHAR); -- NULL
SELECT CAST(JSON '1' AS INTEGER); -- 1
SELECT CAST(JSON '9223372036854775807' AS BIGINT); -- 9223372036854775807
SELECT CAST(JSON '"abc"' AS VARCHAR); -- abc
SELECT CAST(JSON 'true' AS BOOLEAN); -- true
SELECT CAST(JSON '1.234' AS DOUBLE); -- 1.234
SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER)); -- [1, 23, 456]
SELECT CAST(JSON '[1,null,456]' AS ARRAY(INTEGER)); -- [1, NULL, 456]
SELECT CAST(JSON '[[1,23],[456]]' AS ARRAY(ARRAY(INTEGER))); -- [[1, 23], [456]]
SELECT CAST(JSON '{"k1":1,"k2":23,"k3":456}' AS MAP(VARCHAR, INTEGER)); -- {k1=1, k2=23, k3=456}
SELECT CAST(JSON '{"v1":123,"v2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '[123,"abc",true]' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {value1=123, value2=abc, value3=true}

注

JSON配列には混合された要素型を含めることができ、JSONマップには混合された値型を含めることができます。これにより、一部の場合、SQL配列およびマップにキャストすることが不可能になります。これに対処するために、Prestoは配列およびマップの部分的なキャストをサポートしています。

SELECT CAST(JSON '[[1, 23], 456]' AS ARRAY(JSON)); -- [JSON '[1,23]', JSON '456']
SELECT CAST(JSON '{"k1": [1, 23], "k2": 456}' AS MAP(VARCHAR, JSON)); -- {k1 = JSON '[1,23]', k2 = JSON '456'}
SELECT CAST(JSON '[null]' AS ARRAY(JSON)); -- [JSON 'null']

注

JSONからROWにキャストする場合、レガシーサポートのために、RowTypeの二重引用符で囲まれたフィールド名の大文字と小文字は、マッチング時に無視されます。例:

SELECT CAST(JSON '{"v1":123,"V2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '{"v1":123,"V2":"abc","v3":true}' AS ROW(v1 BIGINT, "V2" VARCHAR, "V3" BOOLEAN)); -- {v1=123, V2=abc, V3=true}

次のステートメントは、重複するフィールドのためにエラーを返します。

SELECT CAST(JSON '{"v1":123,"V2":"abc","v2":"abc2","v3":true}' AS ROW(v1 BIGINT, "V2" VARCHAR, v2 VARCHAR, "V3" BOOLEAN));

JSONからROWにキャストする際にRowTypeのフィールド名の大文字と小文字を強制するには、コーディネーターとワーカーの設定プロパティで、設定プロパティlegacy_json_castをfalseに設定します。プロパティを設定した後、二重引用符で囲まれたフィールド名では大文字と小文字が区別され、引用符で囲まれていないフィールド名では大文字と小文字が区別されません。例:

SELECT CAST(JSON '{"v1":123,"V2":"abc","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc, v3=true}
SELECT CAST(JSON '{"v1":123,"V2":"abc","v3":true}' AS ROW(v1 BIGINT, "V2" VARCHAR, v3 BOOLEAN)); -- {v1=123, V2=abc, v3=true}
SELECT CAST(JSON '{"v1":123,"V2":"abc","v3":true}' AS ROW(v1 BIGINT, "v2" VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=null, v3=true}
SELECT CAST(JSON '{"v1":123,"V2":"abc", "v2":"abc2","v3":true}' AS ROW(v1 BIGINT, v2 VARCHAR, "V2" VARCHAR, v3 BOOLEAN)); -- {v1=123, v2=abc2, V2=abc, v3=true}

フィールドの名前が一致しない場合（大文字と小文字の区別を含む）、値はnullになります。

注

JSONからROWにキャストする場合、JSON配列とJSONオブジェクトの両方がサポートされています。

JSON関数¶

is_json_scalar(json) -> boolean()¶

jsonがスカラーであるかどうか（つまり、JSON数値、JSON文字列、true、false、またはnull）を判断します。

SELECT is_json_scalar('1'); -- true
SELECT is_json_scalar('[1, 2, 3]'); -- false

json_array_contains(json, value) -> boolean()¶

valueがjson（JSON配列を含む文字列）に存在するかどうかを判断します。

SELECT json_array_contains('[1, 2, 3]', 2);

json_array_get(json_array, index) -> json()¶

警告

この関数のセマンティクスは壊れています。抽出された要素が文字列の場合、適切に引用符で囲まれていない無効なJSON値に変換されます（値は引用符で囲まれず、内部の引用符はエスケープされません）。

この関数を使用しないことをお勧めします。既存の使用法に影響を与えることなく修正することはできず、将来のリリースで削除される可能性があります。

json_arrayで指定されたインデックスにある要素を返します。インデックスはゼロベースです。

SELECT json_array_get('["a", [3, 9], "c"]', 0); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["a", [3, 9], "c"]', 1); -- JSON '[3,9]'

この関数は、配列の末尾からインデックスが付けられた要素をフェッチするための負のインデックスもサポートしています。

SELECT json_array_get('["c", [3, 9], "a"]', -1); -- JSON 'a' (invalid JSON)
SELECT json_array_get('["c", [3, 9], "a"]', -2); -- JSON '[3,9]'

指定されたインデックスの要素が存在しない場合、関数はnullを返します。

SELECT json_array_get('[]', 0); -- null
SELECT json_array_get('["a", "b", "c"]', 10); -- null
SELECT json_array_get('["c", "b", "a"]', -10); -- null

json_array_length(json) -> bigint()¶

json（JSON配列を含む文字列）の配列の長さを返します。

SELECT json_array_length('[1, 2, 3]');

json_extract(json, json_path) -> json()¶

json（JSONを含む文字列）に対して、JSONPathのような式 json_path を評価し、その結果をJSON文字列として返します。

SELECT json_extract(json, '$.store.book');

json_extract_scalar(json, json_path) -> varchar()¶

json_extract() と同様ですが、結果の値を（JSONとしてエンコードされるのではなく）文字列として返します。 json_path によって参照される値は、スカラー（真偽値、数値、または文字列）でなければなりません。

SELECT json_extract_scalar('[1, 2, 3]', '$[2]');
SELECT json_extract_scalar(json, '$.store.book[0].author');

json_format(json) -> varchar()¶

入力JSON値からシリアライズされたJSONテキストを返します。これは json_parse() の逆関数です。

SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]'
SELECT json_format(JSON '"a"'); -- '"a"'

注

json_format() と CAST(json AS VARCHAR) は、完全に異なるセマンティクスを持ちます。

json_format() は、入力JSON値をRFC 7159に準拠したJSONテキストにシリアライズします。 JSON値は、JSONオブジェクト、JSON配列、JSON文字列、JSON数値、true、false、または null である可能性があります。

SELECT json_format(JSON '{"a": 1, "b": 2}'); -- '{"a":1,"b":2}'
SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]'
SELECT json_format(JSON '"abc"'); -- '"abc"'
SELECT json_format(JSON '42'); -- '42'
SELECT json_format(JSON 'true'); -- 'true'
SELECT json_format(JSON 'null'); -- 'null'

CAST(json AS VARCHAR) は、JSON値を対応するSQL VARCHAR値にキャストします。 JSON文字列、JSON数値、true、false、または null の場合、キャストの動作は対応するSQL型と同じです。 JSONオブジェクトとJSON配列はVARCHARにキャストできません。

SELECT CAST(JSON '{"a": 1, "b": 2}' AS VARCHAR); -- ERROR!
SELECT CAST(JSON '[1, 2, 3]' AS VARCHAR); -- ERROR!
SELECT CAST(JSON '"abc"' AS VARCHAR); -- 'abc'; Note the double quote is gone
SELECT CAST(JSON '42' AS VARCHAR); -- '42'
SELECT CAST(JSON 'true' AS VARCHAR); -- 'true'
SELECT CAST(JSON 'null' AS VARCHAR); -- NULL

json_parse(string) -> json()¶

入力JSONテキストからデシリアライズされたJSON値を返します。これは json_format() の逆関数です。

SELECT json_parse('[1, 2, 3]'); -- JSON '[1,2,3]'
SELECT json_parse('"abc"'); -- JSON '"abc"'

注

json_parse() と CAST(string AS JSON) は、完全に異なるセマンティクスを持ちます。

json_parse() は、RFC 7159に準拠したJSONテキストを期待し、JSONテキストからデシリアライズされたJSON値を返します。 JSON値は、JSONオブジェクト、JSON配列、JSON文字列、JSON数値、true、false、または null である可能性があります。

SELECT json_parse('not_json'); -- ERROR!
SELECT json_parse('{"a": 1, "b": 2}'); -- JSON '{"a": 1, "b": 2}'
SELECT json_parse('[1, 2, 3]'); -- JSON '[1,2,3]'
SELECT json_parse('"abc"'); -- JSON '"abc"'
SELECT json_parse('42'); -- JSON '42'
SELECT json_parse('true'); -- JSON 'true'
SELECT json_parse('null'); -- JSON 'null'

CAST(string AS JSON) は、任意のVARCHAR値を入力として受け取り、その値を入力文字列に設定したJSON文字列を返します。

SELECT CAST('not_json' AS JSON); -- JSON '"not_json"'
SELECT CAST('{"a": 1, "b": 2}' AS JSON); -- JSON '"{\"a\": 1, \"b\": 2}"'
SELECT CAST('[1, 2, 3]' AS JSON); -- JSON '"[1, 2, 3]"'
SELECT CAST('"abc"' AS JSON); -- JSON '"\"abc\""'
SELECT CAST('42' AS JSON); -- JSON '"42"'
SELECT CAST('true' AS JSON); -- JSON '"true"'
SELECT CAST('null' AS JSON); -- JSON '"null"'

json_size(json, json_path) -> bigint()¶

json_extract() と同様ですが、値のサイズを返します。オブジェクトまたは配列の場合、サイズはメンバーの数であり、スカラー値のサイズはゼロです。

SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x'); -- 2
SELECT json_size('{"x": [1, 2, 3]}', '$.x'); -- 3
SELECT json_size('{"x": {"a": 1, "b": 2}}', '$.x.a'); -- 0