PostgreSQL - MacKittipat/note-developer GitHub Wiki

JSON VS JSONB

  • JSON type stores an exact copy of the input text, it will preserve semantically-insignificant white space between tokens, as well as the order of keys within JSON objects
  • JSONB does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.

Operator

Containment Value

jsonb @> jsonb → boolean : Does the first JSON value contain the second ?

SELECT '[1, 2, 3]'::jsonb @> '[1]'::jsonb; -- true
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb; -- true
SELECT '[1, 2, 3]'::jsonb @> '[1, 3, 2]'::jsonb; -- true
SELECT '[1, 2, 3]'::jsonb @> '[4]'::jsonb; -- false
SELECT '[1, 2, 3]'::jsonb @> '[1, 4]'::jsonb; -- false
SELECT '[1, [2, 3]]'::jsonb @> '[2, 3]'::jsonb; -- false
SELECT '[1, [2, 3]]'::jsonb @> '[2, 3](/MacKittipat/note-developer/wiki/2,-3)'::jsonb; -- true
SELECT '[1, [2, 3]]'::jsonb @> '[3](/MacKittipat/note-developer/wiki/3)'::jsonb; -- true

SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb; -- true
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 10.10}'::jsonb; -- false

Containment Key

jsonb ? text → boolean : Does the text string exist as a top-level key or array element within the JSON value?

SELECT '{"a":1, "b":2}'::jsonb ? 'b'; -- true
SELECT '{"a":1, "b":2}'::jsonb ? 'c'; -- false
SELECT '["a", "b", "c"]'::jsonb ? 'b'; -- true

Processing

select * from json_each_text('{"a":"foo", "b":"bar", "c": {"d": "zzz"}}');

select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r text);

Reference