Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/sql-reference/functions/try_parse_json below:

Website Navigation

• Reference
• Function and stored procedure reference
• Semi-structured and structured data

TRY_PARSE_JSON | Snowflake Documentation

Categories:

Semi-structured and structured data functions (Parsing)

A special version of PARSE_JSON that returns a NULL value if an error occurs during parsing.

TRY_PARSE_JSON( <expr> [ , '<parameter>' ] )

Copy

Required:

expr

An expression of string type (for example, VARCHAR) that holds valid JSON information.

Optional:

'parameter'

String constant that specifies the parameter used to search for matches. Supported values:

Parameter

Description

d

Allow duplicate keys in JSON objects. If a JSON object contains a duplicate key, the returned object has a single instance of that key with the last value specified for that key.

s

Don’t allow duplicate keys in JSON objects (strict). This value is the default.

Returns a value of type VARIANT that contains a JSON document.

If the input is NULL or if an error occurs during parsing, the function returns NULL.

This function doesn’t return a structured type.

See PARSE_JSON for the usage notes.

This shows an example of storing different types of data in a VARIANT column by calling TRY_PARSE_JSON to parse strings that contain values that can be parsed as JSON:

Create and fill a table.

CREATE OR REPLACE TEMPORARY TABLE vartab (ID INTEGER, v VARCHAR);

INSERT INTO vartab (id, v) VALUES
  (1, '[-1, 12, 289, 2188, FALSE,]'),
  (2, '{ "x" : "abc", "y" : FALSE, "z": 10} '),
  (3, '{ "bad" : "json", "missing" : TRUE, "close_brace": 10 ');

Copy

Query the data, using TRY_PARSE_JSON. Note that the value for the third line is NULL. If the query used PARSE_JSON rather than TRY_PARSE_JSON, it would fail.

SELECT ID, TRY_PARSE_JSON(v)
  FROM vartab
  ORDER BY ID;

Copy

+----+-------------------+
| ID | TRY_PARSE_JSON(V) |
|----+-------------------|
|  1 | [                 |
|    |   -1,             |
|    |   12,             |
|    |   289,            |
|    |   2188,           |
|    |   false,          |
|    |   undefined       |
|    | ]                 |
|  2 | {                 |
|    |   "x": "abc",     |
|    |   "y": false,     |
|    |   "z": 10         |
|    | }                 |
|  3 | NULL              |
+----+-------------------+

See PARSE_JSON for more examples.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4