: (colon sign) operator

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Extracts content from a JSON string using a JSON path expression.

Syntax 

jsonExpr : jsonPath

Arguments

  • jsonExpr: A VARIANT expression or STRING expression with valid JSON.

  • jsonPath: A JSON path expression.

Returns

The result matches the type of jsonExpr. If the input is not valid JSON or the path expression is not valid for the JSON value the result is NULL. If the extracted value is an un-delimited null the result is the NULL value.

See JSON path expression for a detailed explanation of valid JSON paths.

Examples 

> SELECT c1:price
    FROM VALUES('{ "price": 5 }') AS T(c1);
 5

> SELECT c1:['price']::decimal(5,2)
    FROM VALUES('{ "price": 5 }') AS T(c1);
 5.00

> SELECT c1:item[1].price::double
    FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 },
                             { "model" : "medium", "price" : 9.24 } ] }') AS T(c1);
 9.24

> SELECT c1:item[*].price
    FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 },
                             { "model" : "medium", "price" : 9.24 } ] }') AS T(c1);
 [6.12,9.24]

> SELECT from_json(c1:item[*].price, 'ARRAY<DOUBLE>')[0]
      FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 },
                               { "model" : "medium", "price" : 9.24 } ] }') AS T(c1);
  6.12

> SELECT from_json(c1:item[*], 'ARRAY<STRUCT<model STRING, price DOUBLE>>')
      FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 },
                               { "model" : "medium", "price" : 9.24 } ] }') AS T(c1);
  [{"model":"basic","price":6.12},{"model":"medium","price":9.24}]

> SELECT inline(from_json(c1:item[*], 'ARRAY<STRUCT<model STRING, price DOUBLE>>'))
    FROM VALUES('{ "item": [ { "model" : "basic", "price" : 6.12 },
                               { "model" : "medium", "price" : 9.24 } ] }') AS T(c1);
  basic     6.12
  medium    9.24

-- Examples with VARIANT expressions.
> SELECT PARSE_JSON('{ "price": 5 }'):price
 5

> SELECT PARSE_JSON('{ "price": 5 }'):price::decimal(5,2)
 5.00

> SELECT PARSE_JSON('{ "item": [ { "model" : "basic", "price" : 6.12 },
                             { "model" : "medium", "price" : 9.24 } ] }'):item[1].price::double
 9.24