red_json_extract

red_json_extract

Extracts specific values from a JSON string or expression using JSON-Path syntax.

  • Parameters:

    • p_string (str) – The input JSON string (e.g., '{"test": "val"}') or an expression.

    • p_expression (bool) – Set to True if p_string is an expression to be evaluated.

    • p_json_path (str) – One or more JSON-Path strings (e.g., '$.test' or '$[*].id'). Supports aggregate functions like .concat(), .list(), and .count().

  • returns: emits – Multiple columns containing the extracted values as defined in the EMITS clause.

  • raises RequestException: In case of invalid JSON, malformed paths, or execution errors.

Examples

Basic path extraction

1select red_json_extract('{"test": "dd"}', false, '$.test')
2emits (jresult varchar(1000));

Array handling and wildcards

1select red_json_extract('[{"a": 1, "b": 2}, {"a": 3}]', false, '$[*].a', '$[*].b')
2emits (val_a varchar(1000), val_b varchar(1000));

Using JSON-Path aggregate functions

1-- Concatenate array values into a single string
2select red_json_extract('["2","3"]', false, '$[*].concat()')
3emits (jresult varchar(1000)); -- Result: '23'
4
5-- Count elements in a list
6select red_json_extract('{"tokens": [{"level": "1"}, {"level": "3"}]}', false, '$.tokens[*].level.count()')
7emits (jcount decimal(3,0)); -- Result: 2

Complex nested extraction from an expression

1select red_json_extract('get_url_data(''{"URL": "https://api.example.com"}'')', true, '$.result.name', '$.result.tags[*]')
2emits (name varchar(1000), tags varchar(1000));