JSON (JavaScript Object Notation)

永井忠一 2024.10.10

ドキュメント

Introducing JSON（JSON の紹介）
JavaScript
- JSON
Perl
- JSON - JSON (JavaScript Object Notation) encoder/decoder
- JSON::XS - JSON serialising/deserialising, done correctly and fast
- JSON::PP - JSON::XS compatible pure-Perl module.
Python
- json --- JSON エンコーダーとデコーダー
PostgreSQL
- JSONデータ型
- JSON関数と演算子
SQLite
- JSON Functions And Operators

データ型の対応

JSON 形式との対応

	JavaScript	Perl	Python	PostgreSQL	SQLite
object	`> JSON.stringify({a: 1, b: 2}) '{"a":1,"b":2}' > typeof JSON.parse('{"a":1,"b":2}') "object"`	`$ perl -MJSON -le 'print ref(decode_json(q({"a":1,"b":2})))' HASH`	`In [1]: import json ...: type(json.loads('{"a":1,"b":2}')) Out[1]: dict`	`=> SELECT pg_typeof(CAST('{"a":1,"b":2}' AS json)); pg_typeof ----------- json (1 row) => SELECT json_typeof(CAST('{"a":1,"b":2}' AS json)); json_typeof ------------- object (1 row)`	`sqlite> SELECT typeof(json('{"a":1,"b":2}')->>'$'); text sqlite> SELECT json_type(json('{"a":1,"b":2}')); object`
array	`> JSON.stringify([1, 2, 3]) "[1,2,3]" > typeof(JSON.parse("[1,2,3]")) "object" > Array.isArray(JSON.parse("[1,2,3]")) true`	`$ perl -MJSON -le 'print ref(decode_json("[1,2,3]"))' ARRAY`	`In [2]: type(json.loads('[1,2,3]')) Out[2]: list`	`=> SELECT pg_typeof(CAST('[1,2,3]' AS json)); pg_typeof ----------- json (1 row) => SELECT json_typeof(CAST('[1,2,3]' AS json)); json_typeof ------------- array (1 row)`	`sqlite> SELECT typeof(json('[1,2,3]')->>'$'); text sqlite> SELECT json_type(json('[1,2,3]')); array`
string	`> JSON.stringify("abc") '"abc"' > typeof JSON.parse('"abc"') "string"`	`$ perl -MJSON -le 'print ref(\decode_json(q("abc")))' SCALAR $ perl -MJSON -le 'print decode_json(q("abc")) eq "abc"' 1`	`In [3]: type(json.loads('"abc"')) Out[3]: str`	`=> SELECT pg_typeof(CAST('"abc"' AS json)); pg_typeof ----------- json (1 row) => SELECT json_typeof(CAST('"abc"' AS json)); json_typeof ------------- string (1 row) => SELECT CAST((CAST('"abc"' AS json) #>> '{}') AS text) = 'abc'; ?column? ---------- t (1 row)`	`sqlite> SELECT typeof(json('"abc"')->>'$'); text sqlite> SELECT json_type(json('"abc"')); text`
number	`> JSON.stringify(123.0) "123" > typeof JSON.parse("123") "number" > JSON.parse("123") === 123.0 true`	`$ perl -MJSON -le 'print ref(\decode_json("123"))' SCALAR $ perl -MJSON -MScalar::Util -le \ > 'print Scalar::Util::looks_like_number(decode_json("123"))' 1 $ perl -MJSON -le 'print decode_json("123") == 123.0' 1`	`In [4]: type(json.loads('123')) Out[4]: int In [5]: type(json.loads('123.0')) Out[5]: float`	`=> SELECT pg_typeof(CAST('123' AS json)); pg_typeof ----------- json (1 row) => SELECT json_typeof(CAST('123' AS json)); json_typeof ------------- number (1 row) => SELECT CAST((CAST('123' AS json) #>> '{}') AS INTEGER) = 123; ?column? ---------- t (1 row) => SELECT CAST((CAST('123.0' AS json) #>> '{}') AS REAL) = 123.0; ?column? ---------- t (1 row)`	`sqlite> SELECT typeof(json('123')->>'$'); integer sqlite> SELECT json_type(json('123')); integer sqlite> SELECT typeof(json('123.0')->>'$'); real sqlite> SELECT json_type(json('123.0')); real`
true	`> JSON.stringify(true) "true" > typeof JSON.parse("true") "boolean" > JSON.parse("true") === true true`	`$ perl -MJSON -le 'print ref(decode_json("true"))' JSON::PP::Boolean $ perl -MJSON -le 'print JSON::is_bool(decode_json("true"))' 1`	`In [6]: type(json.loads('true')) Out[6]: bool In [7]: json.loads('true') is True Out[7]: True`	`=> SELECT pg_typeof(CAST('true' AS json)); pg_typeof ----------- json (1 row) => SELECT json_typeof(CAST('true' AS json)); json_typeof ------------- boolean (1 row) => SELECT CAST((CAST('true' AS json) #>> '{}') AS BOOLEAN) = TRUE; ?column? ---------- t (1 row)`	`sqlite> SELECT typeof(json('true')->>'$'); integer sqlite> SELECT json_type(json('true')); true sqlite> SELECT json('true')->>'$' = TRUE; 1`
false	`> JSON.stringify(false) "false" > typeof JSON.parse("false") "boolean" > JSON.parse("false") === false true`	`$ perl -MJSON -le 'print ref(decode_json("false"))' JSON::PP::Boolean $ perl -MJSON -le 'print JSON::is_bool(decode_json("false"))' 1`	`In [8]: type(json.loads('false')) Out[8]: bool In [9]: json.loads('false') is False Out[9]: True`	`=> SELECT pg_typeof(CAST('false' AS json)); pg_typeof ----------- json (1 row) => SELECT json_typeof(CAST('false' AS json)); json_typeof ------------- boolean (1 row) => SELECT CAST((CAST('false' AS json) #>> '{}') AS BOOLEAN) = FALSE; ?column? ---------- t (1 row)`	`sqlite> SELECT typeof(json('false')->>'$'); integer sqlite> SELECT json_type(json('false')); false sqlite> SELECT json('false')->>'$' = FALSE; 1`
null	`> JSON.stringify(null) "null" > typeof JSON.parse("null") "object" > JSON.parse("null") === null true`	`$ perl -MJSON -le 'print ref(\decode_json("null"))' SCALAR $ perl -MJSON -le 'print not defined(decode_json("null"))' 1`	`In [10]: type(json.loads('null')) Out[10]: NoneType In [11]: json.loads('null') is None Out[11]: True`	`=> SELECT pg_typeof(CAST('null' AS json)); pg_typeof ----------- json (1 row) => SELECT json_typeof(CAST('null' AS json)); json_typeof ------------- null (1 row) => SELECT (CAST('null' AS json) #>> '{}') IS NULL; ?column? ---------- t (1 row)`	`sqlite> SELECT typeof(json('null')->>'$'); null sqlite> SELECT json_type(json('null')); null sqlite> SELECT json('null')->>'$' IS NULL; 1`

結果

	JavaScript	Perl	Python	SQL
object	object	HASH	dict
array	object	ARRAY	list
string	string	SCALAR	str	text
number	number		int	INTEGER
number	number		float	REAL
true	boolean		bool	BOOLEAN
false	boolean		bool	BOOLEAN
null	object		NoneType

（確認に使用したソフトウェア、バージョン）

JavaScript

$ deno
Deno 1.44.1
exit using ctrl+d, ctrl+c, or close()
REPL is running with all permissions allowed.
To specify permissions, run `deno repl` with allow flags.
>

$ firefox --version
Mozilla Firefox 129.0.2

Perl

$ perl --version

This is perl 5, version 38, subversion 2 (v5.38.2) built for x86_64-linux-gnu-thread-multi
(with 44 registered patches, see perl -V for more detail)

Copyright 1987-2023, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl".  If you have access to the
Internet, point your browser at https://www.perl.org/, the Perl Home Page.

Python

$ ipython3
Python 3.12.3 (main, Sep 11 2024, 14:17:37) [GCC 13.2.0]
Type 'copyright', 'credits' or 'license' for more information
IPython 8.20.0 -- An enhanced Interactive Python. Type '?' for help.

In [1]:

$ pypy3
Python 3.9.18 (7.3.15+dfsg-1build3, Apr 01 2024, 03:12:48)
[PyPy 7.3.15 with GCC 13.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>>

SQL

PostgreSQL

$ psql
psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2))
Type "help" for help.

=>

SQLite

$ sqlite3
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

JSON 文字列の作成

	JavaScript	Perl	Python	PostgreSQL	SQLite
object	`> JSON.stringify({a: 1, b: 2}) '{"a":1,"b":2}'`	`$ perl -MJSON -le 'print encode_json({"a" => 1, "b" => 2})' {"a":1,"b":2}`	`In [1]: import json ...: json.dumps({'a': 1, 'b': 2}) Out[1]: '{"a": 1, "b": 2}'`	`=> SELECT json_object('a': 1, 'b': 2) #>> '{}'; ?column? -------------------- {"a" : 1, "b" : 2} (1 row) => SELECT json_build_object('a', 1, 'b', 2) #>> '{}'; ?column? -------------------- {"a" : 1, "b" : 2} (1 row)`	`sqlite> SELECT json_object('a', 1, 'b', 2); {"a":1,"b":2}`
array	`> JSON.stringify([1, 2, 3]) "[1,2,3]"`	`$ perl -MJSON -le 'print encode_json([1, 2, 3])' [1,2,3]`	`In [2]: json.dumps([1, 2, 3]) Out[2]: '[1, 2, 3]'`	`=> SELECT json_array(1, 2, 3) #>> '{}'; ?column? ----------- [1, 2, 3] (1 row) => SELECT json_array(VALUES(1), (2), (3)) #>> '{}'; ?column? ----------- [1, 2, 3] (1 row)`	`sqlite> SELECT json_array(1, 2, 3); [1,2,3]`
string	`> JSON.stringify("abc") '"abc"'`	`$ perl -MJSON -le 'print encode_json("abc")' "abc"`	`In [3]: json.dumps('abc') Out[3]: '"abc"'`	`=> SELECT to_json(CAST('abc' AS text)) #>> '{}'; ?column? ---------- abc (1 row)`	`sqlite> SELECT json_quote('abc'); "abc"`
number	`> JSON.stringify(123.0) "123"`	`$ perl -MJSON -le 'print encode_json(123.0)' 123`	`In [4]: json.dumps(123) Out[4]: '123' In [5]: json.dumps(123.0) Out[5]: '123.0'`	`=> SELECT to_json(123) #>> '{}'; ?column? ---------- 123 (1 row) => SELECT to_json(123.0) #>> '{}'; ?column? ---------- 123.0 (1 row)`	`sqlite> SELECT json_quote(123); 123 sqlite> SELECT json_quote(123.0); 123.0`
true	`> JSON.stringify(true) "true"`	`$ perl -MJSON -le 'print encode_json(JSON::true)' true $ perl -MJSON -le 'print encode_json(\1)' true`	`In [6]: json.dumps(True) Out[6]: 'true'`	`=> SELECT to_json(TRUE) #>> '{}'; ?column? ---------- true (1 row)`	`sqlite> SELECT json_quote(TRUE); 1 sqlite> SELECT json_quote(FALSE); 0` （※SQLite では、BOOLEAN 型は INTEGER 型）
false	`> JSON.stringify(false) "false"`	`$ perl -MJSON -le 'print encode_json(JSON::false)' false $ perl -MJSON -le 'print encode_json(\0)' false`	`In [7]: json.dumps(False) Out[7]: 'false'`	`=> SELECT to_json(FALSE) #>> '{}'; ?column? ---------- false (1 row)`
null	`> JSON.stringify(null) "null"`	`$ perl -MJSON -le 'print encode_json(JSON::null)' null $ perl -MJSON -le 'print encode_json(undef)' null`	`In [8]: json.dumps(None) Out[8]: 'null'`	`=> SELECT COALESCE(NULL, CAST(CAST('null' AS json) AS text)); coalesce ---------- null (1 row)`	`sqlite> SELECT json_quote(NULL); null`

（Perl の JSON モジュールの関数名）

decode_json()

$ perl -MJSON::PP -le 'print from_json(q("abc"))'
JSON::PP::from_json has been renamed to decode_json. at -e line 1.

encode_json()

$ perl -MJSON::PP -le 'print to_json("abc")'
JSON::PP::to_json has been renamed to encode_json. at -e line 1.

（PostgreSQL の「 #>>」演算子を使った例は、CAST() 関数による text 型への型変換として書くこともできる）

NaN と Infinity の扱い

RFC 8259 では、許可されていない

Numeric values that cannot be represented in the grammar below (such
as Infinity and NaN) are not permitted.
...

各メソッド、関数の実行結果

JavaScript Perl Python PostgreSQL SQLite

JavaScript	Perl	Python	PostgreSQL	SQLite
`> JSON.stringify(NaN) "null" > JSON.stringify(Infinity) "null" > JSON.stringify(-Infinity) "null"`	`$ perl -le 'print(9e999/9e999)' NaN $ perl -MJSON::XS -le 'print(encode_json(9e999/9e999))' -nan $ perl -MJSON::PP -le 'print(encode_json(9e999/9e999))' NaN` `$ perl -le 'print(9e999)' Inf $ perl -MJSON -le 'print(encode_json(9e999))' inf` `$ perl -le 'print(-9e999)' -Inf $ perl -MJSON -le 'print(encode_json(-9e999))' -inf`	`In [1]: import json ...: json.dumps(float('nan')) Out[1]: 'NaN' In [2]: json.dumps(float('inf')) Out[2]: 'Infinity' In [3]: json.dumps(float('-inf')) Out[3]: '-Infinity'`	`=> SELECT to_json(CAST('NaN' AS real)); to_json --------- "NaN" (1 row) => SELECT json_typeof(to_json(CAST('NaN' AS real))); json_typeof ------------- string (1 row)` `=> SELECT to_json(CAST('Infinity' AS real)); to_json ------------ "Infinity" (1 row) => SELECT json_typeof(to_json(CAST('Infinity' AS real))); json_typeof ------------- string (1 row)` `=> SELECT to_json(CAST('-Infinity' AS real)); to_json ------------- "-Infinity" (1 row) => SELECT json_typeof(to_json(CAST('-Infinity' AS real))); json_typeof ------------- string (1 row)`	`sqlite> SELECT 9e999/9e999; sqlite> SELECT typeof(9e999/9e999); null sqlite> SELECT (9e999/9e999) IS NULL; 1 sqlite> SELECT json_quote(9e999/9e999); null sqlite> SELECT json_type(json_quote(9e999/9e999)); null` `sqlite> SELECT 9e999; Inf sqlite> SELECT typeof(9e999); real sqlite> SELECT json_quote(9e999); 9.0e+999 sqlite> SELECT json_type(json_quote(9e999)); real` `sqlite> SELECT -9e999; -Inf sqlite> SELECT typeof(-9e999); real sqlite> SELECT json_quote(-9e999); -9.0e+999 sqlite> SELECT json_type(json_quote(-9e999)); real`

> JSON.stringify(NaN)
"null"
> JSON.stringify(Infinity)
"null"
> JSON.stringify(-Infinity)
"null"

$ perl -le 'print(9e999/9e999)'
NaN
$ perl -MJSON::XS -le 'print(encode_json(9e999/9e999))'
-nan
$ perl -MJSON::PP -le 'print(encode_json(9e999/9e999))'
NaN

$ perl -le 'print(9e999)'
Inf
$ perl -MJSON -le 'print(encode_json(9e999))'
inf

$ perl -le 'print(-9e999)'
-Inf
$ perl -MJSON -le 'print(encode_json(-9e999))'
-inf

In [1]: import json
   ...: json.dumps(float('nan'))
Out[1]: 'NaN'

In [2]: json.dumps(float('inf'))
Out[2]: 'Infinity'

In [3]: json.dumps(float('-inf'))
Out[3]: '-Infinity'

=> SELECT to_json(CAST('NaN' AS real));
 to_json
---------
 "NaN"
(1 row)

=> SELECT json_typeof(to_json(CAST('NaN' AS real)));
 json_typeof
-------------
 string
(1 row)

=> SELECT to_json(CAST('Infinity' AS real));
  to_json
------------
 "Infinity"
(1 row)

=> SELECT json_typeof(to_json(CAST('Infinity' AS real)));
 json_typeof
-------------
 string
(1 row)

=> SELECT to_json(CAST('-Infinity' AS real));
   to_json
-------------
 "-Infinity"
(1 row)

=> SELECT json_typeof(to_json(CAST('-Infinity' AS real)));
 json_typeof
-------------
 string
(1 row)

sqlite> SELECT 9e999/9e999;

sqlite> SELECT typeof(9e999/9e999);
null
sqlite> SELECT (9e999/9e999) IS NULL;
1
sqlite> SELECT json_quote(9e999/9e999);
null
sqlite> SELECT json_type(json_quote(9e999/9e999));
null

sqlite> SELECT 9e999;
Inf
sqlite> SELECT typeof(9e999);
real
sqlite> SELECT json_quote(9e999);
9.0e+999
sqlite> SELECT json_type(json_quote(9e999));
real

sqlite> SELECT -9e999;
-Inf
sqlite> SELECT typeof(-9e999);
real
sqlite> SELECT json_quote(-9e999);
-9.0e+999
sqlite> SELECT json_type(json_quote(-9e999));
real

（Perl の JSON::XS モジュールでは、NaN が「-nan」とエンコードされている）

（Python の json.dumps() メソッドで「allow_nan=False」オプションを指定した場合には、ValueError となる)

NaN

ValueError: Out of range float values are not JSON compliant: nan

Infinity

ValueError: Out of range float values are not JSON compliant: inf

ValueError: Out of range float values are not JSON compliant: -inf

（SQLite では、NaN（となる演算の結果）が NULL になる）

各メソッド、関数のパース結果

JavaScript Perl Python PostgreSQL SQLite

JavaScript	Perl	Python	PostgreSQL	SQLite
`> JSON.parse("NaN") Uncaught SyntaxError: "NaN" is not valid JSON at JSON.parse (<anonymous>) at <anonymous>:1:27` `> JSON.parse("Infinity") Uncaught SyntaxError: "Infinity" is not valid JSON at JSON.parse (<anonymous>) at <anonymous>:1:27` `> JSON.parse("-Infinity") Uncaught SyntaxError: No number after minus sign in JSON at position 1 (line 1 column 2) at JSON.parse (<anonymous>) at <anonymous>:1:27`	`$ perl -MJSON -le 'print decode_json("NaN")' malformed JSON string, neither tag, array, object, number, string or atom, at character offset 0 (before "NaN") at -e line 1.` `$ perl -MJSON -le 'print decode_json("Infinity")' malformed JSON string, neither tag, array, object, number, string or atom, at character offset 0 (before "Infinity") at -e line 1.` `$ perl -MJSON -le 'print decode_json("-Infinity")' malformed number (no digits after initial minus), at character offset 1 (before "Infinity") at -e line 1.`	`In [1]: import json ...: json.loads('NaN') Out[1]: nan` `In [2]: json.loads('Infinity') Out[2]: inf` `In [3]: json.loads('-Infinity') Out[3]: -inf`	`=> SELECT CAST('NaN' AS json); ERROR: invalid input syntax for type json LINE 1: SELECT CAST('NaN' AS json); ^ DETAIL: Token "NaN" is invalid. CONTEXT: JSON data, line 1: NaN` `=> SELECT CAST('Infinity' AS json); ERROR: invalid input syntax for type json LINE 1: SELECT CAST('Infinity' AS json); ^ DETAIL: Token "Infinity" is invalid. CONTEXT: JSON data, line 1: Infinity` `=> SELECT CAST('-Infinity' AS json); ERROR: invalid input syntax for type json LINE 1: SELECT CAST('-Infinity' AS json); ^ DETAIL: Token "-Infinity" is invalid. CONTEXT: JSON data, line 1: -Infinity`	`sqlite> SELECT json_valid('NaN'); 0 sqlite> SELECT json('NaN'); null` `sqlite> SELECT json_valid('Infinity'); 0 sqlite> SELECT json('Infinity'); 9e999` `sqlite> SELECT json_valid('-Infinity'); 0 sqlite> SELECT json('-Infinity'); -9e999`

> JSON.parse("NaN")
Uncaught SyntaxError: "NaN" is not valid JSON
    at JSON.parse (<anonymous>)
    at <anonymous>:1:27

> JSON.parse("Infinity")
Uncaught SyntaxError: "Infinity" is not valid JSON
    at JSON.parse (<anonymous>)
    at <anonymous>:1:27

> JSON.parse("-Infinity")
Uncaught SyntaxError: No number after minus sign in JSON at position 1 (line 1 column 2)
    at JSON.parse (<anonymous>)
    at <anonymous>:1:27

$ perl -MJSON -le 'print decode_json("NaN")'
malformed JSON string, neither tag, array, object, number, string or atom, at character offset 0 (before "NaN") at -e line 1.

$ perl -MJSON -le 'print decode_json("Infinity")'
malformed JSON string, neither tag, array, object, number, string or atom, at character offset 0 (before "Infinity") at -e line 1.

$ perl -MJSON -le 'print decode_json("-Infinity")'
malformed number (no digits after initial minus), at character offset 1 (before "Infinity") at -e line 1.

In [1]: import json
   ...: json.loads('NaN')
Out[1]: nan

In [2]: json.loads('Infinity')
Out[2]: inf

In [3]: json.loads('-Infinity')
Out[3]: -inf

=> SELECT CAST('NaN' AS json);
ERROR:  invalid input syntax for type json
LINE 1: SELECT CAST('NaN' AS json);
                    ^
DETAIL:  Token "NaN" is invalid.
CONTEXT:  JSON data, line 1: NaN

=> SELECT CAST('Infinity' AS json);
ERROR:  invalid input syntax for type json
LINE 1: SELECT CAST('Infinity' AS json);
                    ^
DETAIL:  Token "Infinity" is invalid.
CONTEXT:  JSON data, line 1: Infinity

=> SELECT CAST('-Infinity' AS json);
ERROR:  invalid input syntax for type json
LINE 1: SELECT CAST('-Infinity' AS json);
                    ^
DETAIL:  Token "-Infinity" is invalid.
CONTEXT:  JSON data, line 1: -Infinity

sqlite> SELECT json_valid('NaN');
0
sqlite> SELECT json('NaN');
null

sqlite> SELECT json_valid('Infinity');
0
sqlite> SELECT json('Infinity');
9e999

sqlite> SELECT json_valid('-Infinity');
0
sqlite> SELECT json('-Infinity');
-9e999

（Python では受け入れられる）

（SQLite では、NaN は NULL となる。また、json_valid() 関数が提供されている）

日付時刻の扱い

日付時刻型は、JSON では定義されていない

各メソッド、関数の実行結果

JavaScript Perl Python PostgreSQL SQLite

JavaScript	Perl	Python	PostgreSQL	SQLite
`> JSON.stringify(new Date("1970-01-01T00:00:00.000Z")) '"1970-01-01T00:00:00.000Z"'`	`《下記》`	`In [1]: import json ...: from datetime import datetime ...: json.dumps(datetime.fromisoformat('1970-01-01T00:00:00.000Z'), default=str) Out[1]: '"1970-01-01 00:00:00+00:00"'`	`=> SET TIME ZONE 'UTC'; SET => SHOW TIME ZONE; TimeZone ---------- UTC (1 row) => SELECT to_json(CAST('1970-01-01T00:00:00.000Z' AS TIMESTAMP WITH TIME ZONE)); to_json ----------------------------- "1970-01-01T00:00:00+00:00" (1 row)`	`sqlite> SELECT json_quote(datetime('1970-01-01T00:00:00.000Z', 'subsec')); "1970-01-01 00:00:00.000"`

> JSON.stringify(new Date("1970-01-01T00:00:00.000Z"))
'"1970-01-01T00:00:00.000Z"'

《下記》

In [1]: import json
   ...: from datetime import datetime
   ...: json.dumps(datetime.fromisoformat('1970-01-01T00:00:00.000Z'), default=str)
Out[1]: '"1970-01-01 00:00:00+00:00"'

=> SET TIME ZONE 'UTC';
SET
=> SHOW TIME ZONE;
 TimeZone
----------
 UTC
(1 row)

=> SELECT to_json(CAST('1970-01-01T00:00:00.000Z' AS TIMESTAMP WITH TIME ZONE));
           to_json
-----------------------------
 "1970-01-01T00:00:00+00:00"
(1 row)

sqlite> SELECT json_quote(datetime('1970-01-01T00:00:00.000Z', 'subsec'));
"1970-01-01 00:00:00.000"

（Perl の DateTime は、以下のようになる）

$ perl -MJSON -MDateTime::Format::ISO8601 -le 'print encode_json(DateTime::Format::ISO8601->parse_datetime("1970-01-01T00:00:00.000Z"));'
encountered object '1970-01-01T00:00:00', but neither allow_blessed, convert_blessed nor allow_tags settings are enabled (or TO_JSON/FREEZE method missing) at -e line 1.

（Python でも、オプション「default=str」を指定しないと TypeError になり、文字列には変換されない）

TypeError: Object of type datetime is not JSON serializable

《Perl と Python の日付時刻について》

IS json

JSON として、パースできるかテスト

PostgreSQL

PostgreSQL
`=> SELECT '{"a":1,"b":2}' IS json; ?column? ---------- t (1 row)` `=> SELECT '{"a":1,"b":2}' IS json value; ?column? ---------- t (1 row) => SELECT '{"a":1,"b":2}' IS json scalar; ?column? ---------- f (1 row) => SELECT '{"a":1,"b":2}' IS json array; ?column? ---------- f (1 row) => SELECT '{"a":1,"b":2}' IS json object; ?column? ---------- t (1 row)`	`=> SELECT '[1,2,3]' IS json; ?column? ---------- t (1 row)` `=> SELECT '[1,2,3]' IS json value; ?column? ---------- t (1 row) => SELECT '[1,2,3]' IS json scalar; ?column? ---------- f (1 row) => SELECT '[1,2,3]' IS json array; ?column? ---------- t (1 row) => SELECT '[1,2,3]' IS json object; ?column? ---------- f (1 row)`	`=> SELECT '"abc"' IS json, '123' IS json; ?column? \| ?column? ----------+---------- t \| t (1 row)` `=> SELECT '"abc"' IS json value, '123' IS json value; ?column? \| ?column? ----------+---------- t \| t (1 row) => SELECT '"abc"' IS json scalar, '123' IS json scalar; ?column? \| ?column? ----------+---------- t \| t (1 row) => SELECT '"abc"' IS json array, '123' IS json array; ?column? \| ?column? ----------+---------- f \| f (1 row) => SELECT '"abc"' IS json object, '123' IS json object; ?column? \| ?column? ----------+---------- f \| f (1 row)`	`=> SELECT 'true' IS json, 'false' IS json; ?column? \| ?column? ----------+---------- t \| t (1 row)` `=> SELECT 'true' IS json value, 'false' IS json value; ?column? \| ?column? ----------+---------- t \| t (1 row) => SELECT 'true' IS json scalar, 'false' IS json scalar; ?column? \| ?column? ----------+---------- t \| t (1 row) => SELECT 'true' IS json array, 'false' IS json array; ?column? \| ?column? ----------+---------- f \| f (1 row) => SELECT 'true' IS json object, 'false' IS json object; ?column? \| ?column? ----------+---------- f \| f (1 row)`	`=> SELECT 'null' IS json; ?column? ---------- t (1 row)` `=> SELECT 'null' IS json value; ?column? ---------- t (1 row) => SELECT 'null' IS json scalar; ?column? ---------- t (1 row) => SELECT 'null' IS json array; ?column? ---------- f (1 row) => SELECT 'null' IS json object; ?column? ---------- f (1 row)`

=> SELECT '{"a":1,"b":2}' IS json;
 ?column?
----------
 t
(1 row)

=> SELECT '{"a":1,"b":2}' IS json value;
 ?column?
----------
 t
(1 row)

=> SELECT '{"a":1,"b":2}' IS json scalar;
 ?column?
----------
 f
(1 row)

=> SELECT '{"a":1,"b":2}' IS json array;
 ?column?
----------
 f
(1 row)

=> SELECT '{"a":1,"b":2}' IS json object;
 ?column?
----------
 t
(1 row)

=> SELECT '[1,2,3]' IS json;
 ?column?
----------
 t
(1 row)

=> SELECT '[1,2,3]' IS json value;
 ?column?
----------
 t
(1 row)

=> SELECT '[1,2,3]' IS json scalar;
 ?column?
----------
 f
(1 row)

=> SELECT '[1,2,3]' IS json array;
 ?column?
----------
 t
(1 row)

=> SELECT '[1,2,3]' IS json object;
 ?column?
----------
 f
(1 row)

=> SELECT '"abc"' IS json, '123' IS json;
 ?column? | ?column?
----------+----------
 t        | t
(1 row)

=> SELECT '"abc"' IS json value, '123' IS json value;
 ?column? | ?column?
----------+----------
 t        | t
(1 row)

=> SELECT '"abc"' IS json scalar, '123' IS json scalar;
 ?column? | ?column?
----------+----------
 t        | t
(1 row)

=> SELECT '"abc"' IS json array, '123' IS json array;
 ?column? | ?column?
----------+----------
 f        | f
(1 row)

=> SELECT '"abc"' IS json object, '123' IS json object;
 ?column? | ?column?
----------+----------
 f        | f
(1 row)

=> SELECT 'true' IS json, 'false' IS json;
 ?column? | ?column?
----------+----------
 t        | t
(1 row)

=> SELECT 'true' IS json value, 'false' IS json value;
 ?column? | ?column?
----------+----------
 t        | t
(1 row)

=> SELECT 'true' IS json scalar, 'false' IS json scalar;
 ?column? | ?column?
----------+----------
 t        | t
(1 row)

=> SELECT 'true' IS json array, 'false' IS json array;
 ?column? | ?column?
----------+----------
 f        | f
(1 row)

=> SELECT 'true' IS json object, 'false' IS json object;
 ?column? | ?column?
----------+----------
 f        | f
(1 row)

=> SELECT 'null' IS json;
 ?column?
----------
 t
(1 row)

=> SELECT 'null' IS json value;
 ?column?
----------
 t
(1 row)

=> SELECT 'null' IS json scalar;
 ?column?
----------
 t
(1 row)

=> SELECT 'null' IS json array;
 ?column?
----------
 f
(1 row)

=> SELECT 'null' IS json object;
 ?column?
----------
 f
(1 row)

（重複するキーの確認）

PostgreSQL

PostgreSQL
`=> SELECT '{"aa":1,"aa":2}' IS json WITH UNIQUE KEYS; ?column? ---------- f (1 row) => SELECT '{"aa":1,"aa":2}' IS json WITHOUT UNIQUE KEYS; ?column? ---------- t (1 row) => SELECT '{"aa":1,"aa":2}' IS NOT json WITH UNIQUE KEYS; ?column? ---------- t (1 row)`

=> SELECT '{"aa":1,"aa":2}' IS json WITH UNIQUE KEYS;
 ?column?
----------
 f
(1 row)

=> SELECT '{"aa":1,"aa":2}' IS json WITHOUT UNIQUE KEYS;
 ?column?
----------
 t
(1 row)

=> SELECT '{"aa":1,"aa":2}' IS NOT json WITH UNIQUE KEYS;
 ?column?
----------
 t
(1 row)

キーの列挙

（と、キーの数）

PostgreSQL

PostgreSQL
`=> SELECT json_object_keys('{"a":1,"b":2}'); json_object_keys ------------------ a b (2 rows) => SELECT pg_typeof(json_object_keys('{"a":1,"b":2}')); pg_typeof ----------- text text (2 rows) => SELECT key, pg_typeof(key) FROM json_object_keys('{"a":1,"b":2}') AS key; key \| pg_typeof -----+----------- a \| text b \| text (2 rows)` `=> SELECT count(*) FROM json_object_keys('{"a":1,"b":2}'); count ------- 2 (1 row)`

=> SELECT json_object_keys('{"a":1,"b":2}');
 json_object_keys
------------------
 a
 b
(2 rows)

=> SELECT pg_typeof(json_object_keys('{"a":1,"b":2}'));
 pg_typeof
-----------
 text
 text
(2 rows)

=> SELECT key, pg_typeof(key) FROM json_object_keys('{"a":1,"b":2}') AS key;
 key | pg_typeof
-----+-----------
 a   | text
 b   | text
(2 rows)

=> SELECT count(*) FROM json_object_keys('{"a":1,"b":2}');
 count
-------
     2
(1 row)

（JavaScript の Object、Perl のハッシュ、Python の dict（辞書）のキー）

JavaScript Perl Python

JavaScript	Perl	Python
`> Object.keys(JSON.parse('{"a":1,"b":2}')) [ "a", "b" ]` `> Object.keys(JSON.parse('{"a":1,"b":2}')).length 2`	`$ perl -MJSON -le 'print keys %{decode_json(q({"a":1,"b":2}))}' ab $ perl -MJSON -le 'print join(", ", (keys %{decode_json(q({"a":1,"b":2}))}))' a, b` `$ perl -MJSON -le 'print scalar(%{decode_json(q({"a":1,"b":2}))})' 2`	`In [1]: import json ...: json.loads('{"a":1,"b":2}').keys() Out[1]: dict_keys(['a', 'b']) In [2]: list(json.loads('{"a":1,"b":2}')) Out[2]: ['a', 'b']` `In [3]: len(json.loads('{"a":1,"b":2}')) Out[3]: 2`

> Object.keys(JSON.parse('{"a":1,"b":2}'))
[ "a", "b" ]

> Object.keys(JSON.parse('{"a":1,"b":2}')).length
2

$ perl -MJSON -le 'print keys %{decode_json(q({"a":1,"b":2}))}'
ab
$ perl -MJSON -le 'print join(", ", (keys %{decode_json(q({"a":1,"b":2}))}))'
a, b

$ perl -MJSON -le 'print scalar(%{decode_json(q({"a":1,"b":2}))})'
2

In [1]: import json
   ...: json.loads('{"a":1,"b":2}').keys()
Out[1]: dict_keys(['a', 'b'])

In [2]: list(json.loads('{"a":1,"b":2}'))
Out[2]: ['a', 'b']

In [3]: len(json.loads('{"a":1,"b":2}'))
Out[3]: 2

イテレーション

json_each() 関数

PostgreSQL
`=> SELECT * FROM json_each('{"a":1,"b":2}'); key \| value -----+------- a \| 1 b \| 2 (2 rows)`

（JavaScript、Perl 、Python での、同様の処理）「key、value」の組に展開

JavaScript Perl Python

JavaScript	Perl	Python
`> for (const [key, value] of Object.entries(JSON.parse('{"a":1,"b":2}'))) console.log(key, value) a 1 b 2 undefined`	`$ perl -MJSON -le '$j = decode_json(q({"a":1,"b":2})); while (my ($key, $value) = each %$j) { print "$key $value" }' a 1 b 2`	`In [1]: import json ...: for key, value in json.loads('{"a":1,"b":2}').items(): ...: print(key, value) ...: a 1 b 2`

> for (const [key, value] of Object.entries(JSON.parse('{"a":1,"b":2}'))) console.log(key, value)
a 1
b 2
undefined

$ perl -MJSON -le '$j = decode_json(q({"a":1,"b":2})); while (my ($key, $value) = each %$j) { print "$key $value" }'
a 1
b 2

In [1]: import json
   ...: for key, value in json.loads('{"a":1,"b":2}').items():
   ...:     print(key, value)
   ...:
a 1
b 2

モディファイア

json_insert()、json_replace()、json_set()、json_remove() 関数

SQLite

SQLite
`sqlite> SELECT json_insert(json('[1,2,3]'), '$[#]', 9); [1,2,3,9] sqlite> SELECT json_replace(json('[1,2,3]'), '$[1]', 1); [1,1,3] sqlite> SELECT json_set(json('[1,2,3]'), '$[1]', 1); [1,1,3] sqlite> SELECT json_remove(json('[1,2,3]'), '$[0]'); [2,3]`

sqlite> SELECT json_insert(json('[1,2,3]'), '$[#]', 9);
[1,2,3,9]
sqlite> SELECT json_replace(json('[1,2,3]'), '$[1]', 1);
[1,1,3]
sqlite> SELECT json_set(json('[1,2,3]'), '$[1]', 1);
[1,1,3]
sqlite> SELECT json_remove(json('[1,2,3]'), '$[0]');
[2,3]

配列の長さ

json_array_length() 関数

PostgreSQL	SQLite
`=> SELECT json_array_length(CAST('[1,2,3]' AS json)); json_array_length ------------------- 3 (1 row)`	`sqlite> SELECT json_array_length(json('[1,2,3]')); 3`

配列の全要素取得

json_array_elements()、json_array_elements_text() 関数

PostgreSQL

PostgreSQL
`=> SELECT json_array_elements('[1,2,3]'); json_array_elements --------------------- 1 2 3 (3 rows) => SELECT pg_typeof(json_array_elements(CAST('[1,2,3]' AS json))), pg_typeof(json_array_elements_text(CAST('[1,2,3]' AS json))); pg_typeof \| pg_typeof -----------+----------- json \| text json \| text json \| text (3 rows)`

=> SELECT json_array_elements('[1,2,3]');
 json_array_elements
---------------------
 1
 2
 3
(3 rows)

=> SELECT pg_typeof(json_array_elements(CAST('[1,2,3]' AS json))), pg_typeof(json_array_elements_text(CAST('[1,2,3]' AS json)));
 pg_typeof | pg_typeof
-----------+-----------
 json      | text
 json      | text
 json      | text
(3 rows)

（違いは、json 型で取得するか、text 型で取得するか）

レコードへの変換

json_to_record()、json_to_recordset() 関数

PostgreSQL

PostgreSQL
`=> SELECT * FROM json_to_record(CAST('{"column1":1,"column2":2}' AS json)) AS (column1 INTEGER, column2 INTEGER); column1 \| column2 ---------+--------- 1 \| 2 (1 row) => SELECT * FROM json_to_recordset(CAST('[{"column1":1,"column2":2},{"column1":3,"column2":4}]' AS json)) AS (column1 INTEGER, column2 INTEGER); column1 \| column2 ---------+--------- 1 \| 2 3 \| 4 (2 rows)`

=> SELECT * FROM json_to_record(CAST('{"column1":1,"column2":2}' AS json)) AS (column1 INTEGER, column2 INTEGER);
 column1 | column2
---------+---------
       1 |       2
(1 row)

=> SELECT * FROM json_to_recordset(CAST('[{"column1":1,"column2":2},{"column1":3,"column2":4}]' AS json)) AS (column1 INTEGER, column2 INTEGER);
 column1 | column2
---------+---------
       1 |       2
       3 |       4
(2 rows)

（逆変換）ドットコマンド「.mode json」を利用

SQLite
`sqlite> .mode json sqlite> SELECT * FROM (VALUES(1, 2), (3, 4)); [{"column1":1,"column2":2}, {"column1":3,"column2":4}] sqlite> .mode list`

（SQLite の、json モードのヘルプ）

sqlite> .help mode
.mode MODE ?OPTIONS?     Set output mode
   MODE is one of:
...snip...
     json        Results in a JSON array
...snip...

トラバース

（パス指定）json_extract_path()、json_extract() 関数と、JSON パス式

PostgreSQL SQLite

PostgreSQL	SQLite
`=> SELECT json_extract_path(CAST('{"a":{"c":3},"b":2}' AS json), 'a', 'c'); json_extract_path ------------------- 3 (1 row) => SELECT CAST('{"a":{"c":3},"b":2}' AS json)->'a'->'c'; ?column? ---------- 3 (1 row) => SELECT CAST('{"a":{"c":3},"b":2}' AS json) #> '{a,c}'; ?column? ---------- 3 (1 row)` `=> SELECT json_extract_path(CAST('[[1,2],[3,4]]' AS json), '1', '0'); json_extract_path ------------------- 3 (1 row) => SELECT CAST('[[1,2],[3,4]]' AS json)->1->0; ?column? ---------- 3 (1 row) => SELECT CAST('[[1,2],[3,4]]' AS json) #> '{1,0}'; ?column? ---------- 3 (1 row)` `=> SELECT json_extract_path(CAST('{"a":1,"b":[1,2,3]}' AS json), 'b', '2'); json_extract_path ------------------- 3 (1 row) => SELECT CAST('{"a":1,"b":[1,2,3]}' AS json)->'b'->2; ?column? ---------- 3 (1 row) => SELECT CAST('{"a":1,"b":[1,2,3]}' AS json) #> '{b,2}'; ?column? ---------- 3 (1 row)`	`sqlite> SELECT json_extract(json('{"a":{"c":3},"b":2}'), '$.a.c'); 3 sqlite> SELECT json('{"a":{"c":3},"b":2}')->>'a'->>'c'; 3 sqlite> SELECT json('{"a":{"c":3},"b":2}')->>'$.a.c'; 3` `sqlite> SELECT json_extract(json('[[1,2],[3,4]]'), '$[1][0]'); 3 sqlite> SELECT json('[[1,2],[3,4]]')->>1->>0; 3 sqlite> SELECT json('[[1,2],[3,4]]')->>'$[1][0]'; 3` `sqlite> SELECT json_extract(json('{"a":1,"b":[1,2,3]}'), '$.b[2]'); 3 sqlite> SELECT json('{"a":1,"b":[1,2,3]}')->>'b'->>2; 3 sqlite> SELECT json('{"a":1,"b":[1,2,3]}')->>'$.b[2]'; 3`

=> SELECT json_extract_path(CAST('{"a":{"c":3},"b":2}' AS json), 'a', 'c');
 json_extract_path
-------------------
 3
(1 row)

=> SELECT CAST('{"a":{"c":3},"b":2}' AS json)->'a'->'c';
 ?column?
----------
 3
(1 row)

=> SELECT CAST('{"a":{"c":3},"b":2}' AS json) #> '{a,c}';
 ?column?
----------
 3
(1 row)

=> SELECT json_extract_path(CAST('[[1,2],[3,4]]' AS json), '1', '0');
 json_extract_path
-------------------
 3
(1 row)

=> SELECT CAST('[[1,2],[3,4]]' AS json)->1->0;
 ?column?
----------
 3
(1 row)

=> SELECT CAST('[[1,2],[3,4]]' AS json) #> '{1,0}';
 ?column?
----------
 3
(1 row)

=> SELECT json_extract_path(CAST('{"a":1,"b":[1,2,3]}' AS json), 'b', '2');
 json_extract_path
-------------------
 3
(1 row)

=> SELECT CAST('{"a":1,"b":[1,2,3]}' AS json)->'b'->2;
 ?column?
----------
 3
(1 row)

=> SELECT CAST('{"a":1,"b":[1,2,3]}' AS json) #> '{b,2}';
 ?column?
----------
 3
(1 row)

sqlite> SELECT json_extract(json('{"a":{"c":3},"b":2}'), '$.a.c');
3
sqlite> SELECT json('{"a":{"c":3},"b":2}')->>'a'->>'c';
3
sqlite> SELECT json('{"a":{"c":3},"b":2}')->>'$.a.c';
3

sqlite> SELECT json_extract(json('[[1,2],[3,4]]'), '$[1][0]');
3
sqlite> SELECT json('[[1,2],[3,4]]')->>1->>0;
3
sqlite> SELECT json('[[1,2],[3,4]]')->>'$[1][0]';
3

sqlite> SELECT json_extract(json('{"a":1,"b":[1,2,3]}'), '$.b[2]');
3
sqlite> SELECT json('{"a":1,"b":[1,2,3]}')->>'b'->>2;
3
sqlite> SELECT json('{"a":1,"b":[1,2,3]}')->>'$.b[2]';
3

text 型として取り出す

PostgreSQL SQLite

PostgreSQL	SQLite
`=> SELECT json_extract_path_text(CAST('{"a":{"c":3},"b":2}' AS json), 'a', 'c'); json_extract_path_text ------------------------ 3 (1 row) => SELECT CAST('{"a":{"c":3},"b":2}' AS json)->'a'->>'c'; ?column? ---------- 3 (1 row) => SELECT CAST('{"a":{"c":3},"b":2}' AS json) #>> '{a,c}'; ?column? ---------- 3 (1 row)` `=> SELECT json_extract_path_text(CAST('[[1,2],[3,4]]' AS json), '1', '0'); json_extract_path_text ------------------------ 3 (1 row) => SELECT CAST('[[1,2],[3,4]]' AS json)->1->>0; ?column? ---------- 3 (1 row) => SELECT CAST('[[1,2],[3,4]]' AS json) #>> '{1,0}'; ?column? ---------- 3 (1 row)` `=> SELECT json_extract_path_text(CAST('{"a":1,"b":[1,2,3]}' AS json), 'b', '2'); json_extract_path_text ------------------------ 3 (1 row) => SELECT CAST('{"a":1,"b":[1,2,3]}' AS json)->'b'->>2; ?column? ---------- 3 (1 row) => SELECT CAST('{"a":1,"b":[1,2,3]}' AS json) #>> '{b,2}'; ?column? ---------- 3 (1 row)`	`sqlite> SELECT json('{"a":{"c":3},"b":2}')->>'a'->'c'; 3 sqlite> SELECT json('{"a":{"c":3},"b":2}')->'$.a.c'; 3` `sqlite> SELECT json('[[1,2],[3,4]]')->>1->0; 3 sqlite> SELECT json('[[1,2],[3,4]]')->'$[1][0]'; 3` `sqlite> SELECT json('{"a":1,"b":[1,2,3]}')->>'b'->2; 3 sqlite> SELECT json('{"a":1,"b":[1,2,3]}')->'$.b[2]'; 3`

=> SELECT json_extract_path_text(CAST('{"a":{"c":3},"b":2}' AS json), 'a', 'c');
 json_extract_path_text
------------------------
 3
(1 row)

=> SELECT CAST('{"a":{"c":3},"b":2}' AS json)->'a'->>'c';
 ?column?
----------
 3
(1 row)

=> SELECT CAST('{"a":{"c":3},"b":2}' AS json) #>> '{a,c}';
 ?column?
----------
 3
(1 row)

=> SELECT json_extract_path_text(CAST('[[1,2],[3,4]]' AS json), '1', '0');
 json_extract_path_text
------------------------
 3
(1 row)

=> SELECT CAST('[[1,2],[3,4]]' AS json)->1->>0;
 ?column?
----------
 3
(1 row)

=> SELECT CAST('[[1,2],[3,4]]' AS json) #>> '{1,0}';
 ?column?
----------
 3
(1 row)

=> SELECT json_extract_path_text(CAST('{"a":1,"b":[1,2,3]}' AS json), 'b', '2');
 json_extract_path_text
------------------------
 3
(1 row)

=> SELECT CAST('{"a":1,"b":[1,2,3]}' AS json)->'b'->>2;
 ?column?
----------
 3
(1 row)

=> SELECT CAST('{"a":1,"b":[1,2,3]}' AS json) #>> '{b,2}';
 ?column?
----------
 3
(1 row)

sqlite> SELECT json('{"a":{"c":3},"b":2}')->>'a'->'c';
3
sqlite> SELECT json('{"a":{"c":3},"b":2}')->'$.a.c';
3

sqlite> SELECT json('[[1,2],[3,4]]')->>1->0;
3
sqlite> SELECT json('[[1,2],[3,4]]')->'$[1][0]';
3

sqlite> SELECT json('{"a":1,"b":[1,2,3]}')->>'b'->2;
3
sqlite> SELECT json('{"a":1,"b":[1,2,3]}')->'$.b[2]';
3

（PostgreSQL では、json_extract_path_text() 関数が用意される）

Merge Patch アルゴリズム

json_patch() 関数

SQLite
`sqlite> SELECT json_patch(json('{"a":1,"b":2}'), json('{"b":3,"c":3}')); {"a":1,"b":3,"c":3}`

（詳細は RFC 7396）

jsonb

（PosgreSQL と SQLite ともに、JSON のバイナリ表現がある）

JSON (JavaScript Object Notation)

ドキュメント

データ型の対応

JSON 文字列の作成

NaN と Infinity の扱い

日付 時刻の扱い

IS json

キーの列挙

イテレーション

モディファイア

配列の長さ

配列の全要素 取得

レコードへの変換

トラバース

Merge Patch アルゴリズム

jsonb

日付時刻の扱い

配列の全要素取得