parse_json

On this page

Parses a JSON string into a ScopeQL value.

Syntax

parse_json(<string>)

Arguments

ArgumentTypeDescription
<string>stringValid JSON text. Objects, arrays, scalars, and JSON null are accepted.

Returns

Returns any. JSON null and a NULL input both produce NULL. Invalid JSON raises an error; wrap the call in TRY(...) to return NULL on a parsing error.

Examples

Analyze a serialized checkout payload

Parse JSON text, extract fields from the returned any value, and cast an amount for aggregation.

VALUES
    ('{"channel":"web","total_cents":4900}'),
    ('{"channel":"web","total_cents":7900}'),
    ('{"channel":"mobile","total_cents":2900}')
SELECT parse_json($0) AS payload
SELECT payload['channel']::string AS channel, payload['total_cents']::int AS total_cents
GROUP BY channel
AGGREGATE sum(total_cents) AS sales_cents
ORDER BY channel
+---------+-------------+
| channel | sales_cents |
+---------+-------------+
| mobile  | 2900        |
| web     | 12800       |
+---------+-------------+

Identify malformed producer records

Use TRY to retain a result for every input while inspecting a batch that contains malformed JSON.

VALUES ('evt-17', '{"status":"paid"}'), ('evt-42', '{"status":')
SELECT $0 AS event_id, $1 AS raw_payload
SELECT event_id, TRY(parse_json(raw_payload)) AS payload
+----------+-------------------+
| event_id | payload           |
+----------+-------------------+
| evt-17   | {"status":"paid"} |
| evt-42   | NULL              |
+----------+-------------------+

The malformed record produces NULL. Check for these NULL results before aggregating parsed values.