JSON_EXTRACT_PATH_TEXT

Description

The JSON_EXTRACT_PATH_TEXT function returns the value for the key-value pair referenced by a series of path elements in a JSON string.. (RedShift SQL Language Reference JSON_EXTRACT_PATH_TEXT Function)

Grammar Syntax

JSON_EXTRACT_PATH_TEXT('json_string', 'path_elem' [,'path_elem'[, …] ]              [, null_if_invalid ] )

This function is fully supported by Snowflake.

Sample Source Patterns

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT
'{
  "house": {
    "address": {
      "street": "123 Any St.",
      "city": "Any Town",
      "state": "FL",
      "zip": "32830"
    },
    "bathroom": {
      "color": "green",
      "shower": true
    },
    "appliances": {
      "washing machine": {
        "brand": "Any Brand",
        "color": "beige"
      },
      "dryer": {
        "brand": "Any Brand",
        "color": "white"
      }
    }
  }
}' as VALID_JSON,
'notvalidjson' as INVALID_JSON,
JSON_EXTRACT_PATH_TEXT(VALID_JSON, 'house', 'appliances', 'washing machine', 'brand') AS VALID_JSON_FLAG_DEFAULT_OFF,
JSON_EXTRACT_PATH_TEXT(VALID_JSON, 'house', 'appliances', 'washing machine', 'brand', false) AS VALID_JSON_FLAG_OFF,
JSON_EXTRACT_PATH_TEXT(VALID_JSON, 'house', 'appliances', 'washing machine', 'brand', true) AS VALID_JSON_FLAG_TRUE,
JSON_EXTRACT_PATH_TEXT(INVALID_JSON, 'house', 'appliances', 'washing machine', 'brand', true) AS INVALID_JSON_FLAG_TRUE,
JSON_EXTRACT_PATH_TEXT(INVALID_JSON, 'house', 'appliances', 'washing machine', 'brand', false) AS INVALID_JSON_FLAG_FALSE
;

Results

VALID_JSON	INVALID_JSON	VALID_JSON_FLAG_DEFAULT_OFF	VALID_JSON_FLAG_OFF	VALID_JSON_FLAG_TRUE	INVALID_JSON_FLAG_TRUE
-	notvalidjson	Any Brand	Any Brand	Any Brand	NULL

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
'{
  "house": {
    "address": {
      "street": "123 Any St.",
      "city": "Any Town",
      "state": "FL",
      "zip": "32830"
    },
    "bathroom": {
      "color": "green",
      "shower": true
    },
    "appliances": {
      "washing machine": {
        "brand": "Any Brand",
        "color": "beige"
      },
      "dryer": {
        "brand": "Any Brand",
        "color": "white"
      }
    }
  }
}' as VALID_JSON,
'notvalidjson' as INVALID_JSON,
JSON_EXTRACT_PATH_TEXT(VALID_JSON, ARRAY_TO_STRING(['house', 'appliances', '"washing machine"', 'brand'], '.')) AS VALID_JSON_FLAG_DEFAULT_OFF,
JSON_EXTRACT_PATH_TEXT(VALID_JSON, ARRAY_TO_STRING(['house', 'appliances', '"washing machine"', 'brand'], '.')) AS VALID_JSON_FLAG_OFF,
JSON_EXTRACT_PATH_TEXT(TRY_PARSE_JSON(VALID_JSON), ARRAY_TO_STRING(['house', 'appliances', '"washing machine"', 'brand'], '.')) AS VALID_JSON_FLAG_TRUE,
JSON_EXTRACT_PATH_TEXT(TRY_PARSE_JSON(INVALID_JSON), ARRAY_TO_STRING(['house', 'appliances', '"washing machine"', 'brand'], '.')) AS INVALID_JSON_FLAG_TRUE,
JSON_EXTRACT_PATH_TEXT(INVALID_JSON, ARRAY_TO_STRING(['house', 'appliances', '"washing machine"', 'brand'], '.')) AS INVALID_JSON_FLAG_FALSE
;

Results

VALID_JSON	INVALID_JSON	VALID_JSON_FLAG_DEFAULT_OFF	VALID_JSON_FLAG_OFF	VALID_JSON_FLAG_TRUE	INVALID_JSON_FLAG_TRUE
-	notvalidjson	Any Brand	Any Brand	Any Brand	NULL

Input Code using Variables as paths:

Redshift

IN -> Redshift_02.sql

SELECT
  'appliances' level_2,
  'brand' level_4,
  JSON_EXTRACT_PATH_TEXT(
    INFO.VALID_JSON,
    'house',
    level_2,
    'washing machine',
    level_4
  ) result
FROM
  (
    SELECT
      '{
  "house": {
    "address": {
      "street": "123 Any St.",
      "city": "Any Town",
      "state": "FL",
      "zip": "32830"
    },
    "bathroom": {
      "color": "green",
      "shower": true
    },
    "appliances": {
      "washing machine": {
        "brand": "Any Brand",
        "color": "beige"
      },
      "dryer": {
        "brand": "Any Brand",
        "color": "white"
      }
    }
  }
}' AS VALID_JSON
  ) INFO;

Results

level_2	level_4	result
appliances	brand	Any Brand

Output Code:

Snowflake

OUT -> Redshift_02.sql

SELECT
  'appliances' level_2,
  'brand' level_4,
  JSON_EXTRACT_PATH_TEXT(
  INFO.VALID_JSON, ARRAY_TO_STRING(['house',
  level_2, '"washing machine"',
  level_4], '.')) result
FROM
  (
    SELECT
      '{
  "house": {
    "address": {
      "street": "123 Any St.",
      "city": "Any Town",
      "state": "FL",
      "zip": "32830"
    },
    "bathroom": {
      "color": "green",
      "shower": true
    },
    "appliances": {
      "washing machine": {
        "brand": "Any Brand",
        "color": "beige"
      },
      "dryer": {
        "brand": "Any Brand",
        "color": "white"
      }
    }
  }
}' AS VALID_JSON
  ) INFO;

Results

level_2	level_4	result
appliances	brand	Any Brand

Known Issues

• Redshift treats different characters when they are newline, tab, and carriage compared to Snowflake. Redshift interprets the characters as the characters themselves. Snowflake applies it.

• Snowflake function receives two parameters: the first one is the JSON Literal, and the second one is the path separated by a dot to access the inner objects. The transformation replaces the list of paths into a function that will join the paths separated by dots, even if it is a column reference.

• When the path is sent through a variable, and the variable contains spaces, it should be quoted.

Related EWIs

No issues were found.