SeaCode

Appearance

JSON Function ​

sql
json_extract(x, json_path)
json_extract_scalar(x, json_path)

json_extract returns a JSON-encoded string, which means the output retains its JSON format. It can return complex types, including objects and arrays.

json_extract_scalar returns a scalar value (string, number, or boolean) as a plain string (varchar). It is specifically designed to extract simple values rather than JSON structures.

sql
SELECT json_extract('{"name": "John", "age": 30}', '$') AS full_json; 

--  full_json: {"name":"John","age":30}	

SELECT json_extract_scalar('{"name": "John", "age": 30}', '$') AS full_json;

--  full_json: %null%

SELECT json_extract('{"name": "John", "age": 30}', '$.name') AS name;

-- name: "John"

SELECT json_extract_scalar('{"name": "John", "age": 30}', '$.name') AS name;

-- name: John

Query:

sql
WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},
           {"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract(myblob, '$.name') AS name,
  json_extract(myblob, '$.projects') AS projects
FROM dataset

Result:

+-----------------------------------------------------------------------------------------------+
| name           | projects                                                                     |
+-----------------------------------------------------------------------------------------------+
| "Susan Smith"  | [{"name":"project1","completed":false},{"name":"project2","completed":true}] |
+-----------------------------------------------------------------------------------------------+

Query:

sql
WITH dataset AS (
  SELECT '{"name": "Susan Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT
  json_extract_scalar(myblob, '$.name') AS name,
  json_extract_scalar(myblob, '$.projects') AS projects
FROM dataset

Result:

+---------------------------+
| name           | projects |
+---------------------------+
| Susan Smith    |          |
+---------------------------+

Query:

sql
WITH dataset AS (
  SELECT '{"name": "Bob Smith",
           "org": "engineering",
           "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
    AS myblob
)
SELECT json_array_get(json_extract(myblob, '$.projects'), 0) AS item
FROM dataset

Result:

+---------------------------------------+
| item                                  |
+---------------------------------------+
| {"name":"project1","completed":false} |
+---------------------------------------+

Query:

sql
WITH dataset AS (
   SELECT '{"name": "Bob Smith",
             "org": "engineering",
             "projects": [{"name":"project1", "completed":false},{"name":"project2", "completed":true}]}'
     AS myblob
)
SELECT json_extract_scalar(myblob, '$.projects[0].name') AS project_name
FROM dataset

Result:

+--------------+
| project_name |
+--------------+
| project1     |
+--------------+

References ​