Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.

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')
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.