I'm trying to parse json data in a column with Druid SQL in Superset SQL lab. My table looks like this:
| id | json_scores |
|---|---|
| 0 | {"foo": 20, "bar": 10} |
| 1 | {"foo": 30, "bar": 10} |
I'm looking for something similar to json_extract in MySQL e.g.
SELECT *
FROM my_table
WHERE json_extract(json_scores, '$.foo') > 10;
Druid doesn't support json_extract function. Druid supports only ANSI SQL 92, which does not understand JSON as a data type.
Supported data type are listed in this page: https://docs.imply.io/latest/druid/querying/sql-data-types/
You can use any expressions that are listed here: https://druid.apache.org/docs/latest/misc/math-expr.html#string-functions
In your case consider using regexp_extract:
regexp_extract(json_scprs, '(?<=\"foo\":\s)(\d+)(?=,)', 0) AS foo,