How to parse json data in a column with Druid SQL?

Question

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;

Answer 1

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,