Menu
I have a JSON type column called "log_data" and the data stored in it is in the format [{"key":"test123123","identity":"[email protected]","identity_type":"email"}].
I want to count how many records for a given value for a given key in json:
Doesn't work
SELECT count (distinct esas_logs.log_id) AS "count" FROM "esas_logs" WHERE log_data->0->>'identity' = '[email protected]'
[2016-06-30 13:59:18] [42883] ERROR: operator does not exist: json = unknown
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
431
Querying the JSON documentPostgreSQL has two native operators -> and ->> to query JSON documents. The first operator -> returns a JSON object, while the operator ->> returns text. These operators work on both JSON as well as JSONB columns. There are additional operators available for JSONB columns.
Because JSONB stores data in a binary format, queries process significantly faster. Storing data in binary form allows Postgres to access a particular JSON key-value pair without reading the entire JSON record. The reduced disk load speeds up overall performance.
In general, most applications should prefer to store JSON data as jsonb , unless there are quite specialized needs, such as legacy assumptions about ordering of object keys. RFC 7159 specifies that JSON strings should be encoded in UTF8.
If you're using static JSON data and active data that's structured for SQL storage, Postgres is a good shout — its JSONB representation is efficient and allows for indexing.
use json_array_length()
test=# select json_array_length('[{"key":"test123123","identity":"[email protected]","identity_type":"email"}]');
json_array_length
-------------------
1
(1 row)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
