...

или внеочередные заметки


Previous Entry Share Next Entry
json vs jsonb in one query
trekking, Himalaya
obartunov
PostgreSQL has two json data types - json (string) and jsonb (binary) data types. The differences between these data types can be revealed from this query.

SELECT j::json AS json, j::jsonb AS jsonb FROM 
(SELECT '{"cc":0, "aa":  2, "aa":1,"b":1}' AS j) AS foo;
               json               |           jsonb
----------------------------------+----------------------------
 {"cc":0, "aa":  2, "aa":1,"b":1} | {"b": 1, "aa": 1, "cc": 0}
(1 row)


* json: textual storage «as is»
* jsonb: no whitespaces
* jsonb: no duplicate keys, last key win
* jsonb: keys are sorted (by length, then by key name)

It's possible to write queries using SQL/JSON implementation:


SELECT JSON_OBJECT('cc' VALUE 0, 'aa':  2, 'aa':1,'b':1  RETURNING JSON) as json;
                  json
-----------------------------------------
 {"cc" : 0, "aa" : 2, "aa" : 1, "b" : 1}

SELECT JSON_OBJECT('cc':0, 'aa':  2, 'aa':1,'b':1  RETURNING JSONB) as jsonb;
           jsonb
----------------------------
 {"b": 1, "aa": 1, "cc": 0}

Tags: , ,

?

Log in

No account? Create an account