how to use json_extract_path_text?

Question

I am facing an issue with JSON extract using JSON_EXTRACT_PATH_TEXT in redshift

I have two separate JSON columns One containing the modems the customer is using and the other one containing the recharge details

{"Mnfcr": "Technicolor","Model_Name":"Technicolor ABC1243","Smart_Modem":"Y"} For the above, I have no issue extracting the Model_name using JSON_EXTRACT_PATH_TEXT(COLUMN_NAME, 'Model_Name') as model_name

[{"Date":"2021-12-24 21:42:01","Amt":50.00}] This one is causing me trouble. I used the same method above and it did not work. it gave me the below error ERROR: JSON parsing error Detail: ----------------------------------------------- error: JSON parsing error code: 8001 context: invalid json object [{"Date":"2021-07-03 17:12:16","Amt":50.00

Can I please get assistance on how to extract this using the json_extract_path_text? One other method I have found and it worked was to use regexp_substring.

Answer 1

This second string is a json array (square braces), not an object (curly brackets). The array contains a single element which is an object. So you need to extract the object from the array before using JSON_EXTRACT_PATH_TEXT().

The junction for this is JSON_EXTRACT_ARRAY_ELEMENT_TEXT().

Putting this all together we get:

JSON_EXTRACT_PATH_TEXT(
  JSON_EXTRACT_ARRAY_ELEMENT_TEXT( <column>, 0)
  , 'Amt')

Answer 2

you can use json_extract_path_text like below example

json_extract_path_text(json_columnName, json_keyName) = compareValue

for more you can refer this article

https://docs.aws.amazon.com/redshift/latest/dg/JSON_EXTRACT_PATH_TEXT.html