I am trying to get JSON extract from a column value in redshift.

The column value is like:

[{'IDIndex': '0001', 'History': 4, 'Name': '08-SA-21-C1', 'ActiveFlag': 1, 'Category': 3, 'TotalCount': 0, 'Count1': 0, 'Count2': 0, 'Count3': 0, 'Count4': 0, 'Count5': 0}, {'IDIndex': '0002', 'History': 2, 'Name': '09-GA-5-E1', 'ActiveFlag': 1, 'Category': 1, 'TotalCount': 0, 'Count1': 0, 'Count2': 0, 'Count3': 0, 'Count4': 0, 'Count5': 0}, {'IDIndex': '0003', 'History': 2, 'Name': '12-GA-9-C2', 'ActiveFlag': 1, 'Category': 3, 'TotalCount': 0, 'Count1': 0, 'Count2': 0, 'Count3': 0, 'Count4': 0, 'Count5': 0}]

I need output value like:

IDIndex  History
            
0001       4
0002       2
0003       2

I have tried with

JSON_EXTRACT_PATH_TEXT(
  JSON_EXTRACT_ARRAY_ELEMENT_TEXT( <column>, 0)  , 'IDIndex'). 

But still getting below error:

ERROR: JSON parsing error Detail: ----------------------------------------------- error: JSON parsing error code: 8001 context: invalid json array object [{'IDIndex': '0001', 'History': 4, 'Name': '08-SA-21-C1', 'ActiveFlag': 1, 'Category': 3, 'TotalCount': 0, 'Count1': 0, 'Count2': 0, 'Count3': 0, 'Count4': 0, 'Count5': 0}, {'IDIndex': '0002', 'History': 2, 'Name': '09-GA-5-E1', ' query: 897562381 location: funcs_json.hpp:202 process: query5_191_897562381 [pid=30632] ----------------------------------------------- [ErrorId: 1-67b57803-519935642b414283203e6bde]

Can anyone please help on to resolve this issue.

I am trying to get JSON extract from a column value in redshift.

The column value is like:

[{'IDIndex': '0001', 'History': 4, 'Name': '08-SA-21-C1', 'ActiveFlag': 1, 'Category': 3, 'TotalCount': 0, 'Count1': 0, 'Count2': 0, 'Count3': 0, 'Count4': 0, 'Count5': 0}, {'IDIndex': '0002', 'History': 2, 'Name': '09-GA-5-E1', 'ActiveFlag': 1, 'Category': 1, 'TotalCount': 0, 'Count1': 0, 'Count2': 0, 'Count3': 0, 'Count4': 0, 'Count5': 0}, {'IDIndex': '0003', 'History': 2, 'Name': '12-GA-9-C2', 'ActiveFlag': 1, 'Category': 3, 'TotalCount': 0, 'Count1': 0, 'Count2': 0, 'Count3': 0, 'Count4': 0, 'Count5': 0}]

I need output value like:

IDIndex  History
            
0001       4
0002       2
0003       2

I have tried with

JSON_EXTRACT_PATH_TEXT(
  JSON_EXTRACT_ARRAY_ELEMENT_TEXT( <column>, 0)  , 'IDIndex'). 

But still getting below error:

ERROR: JSON parsing error Detail: ----------------------------------------------- error: JSON parsing error code: 8001 context: invalid json array object [{'IDIndex': '0001', 'History': 4, 'Name': '08-SA-21-C1', 'ActiveFlag': 1, 'Category': 3, 'TotalCount': 0, 'Count1': 0, 'Count2': 0, 'Count3': 0, 'Count4': 0, 'Count5': 0}, {'IDIndex': '0002', 'History': 2, 'Name': '09-GA-5-E1', ' query: 897562381 location: funcs_json.hpp:202 process: query5_191_897562381 [pid=30632] ----------------------------------------------- [ErrorId: 1-67b57803-519935642b414283203e6bde]

Can anyone please help on to resolve this issue.

• amazon-redshift
• amazon-redshift-spectrum
• json-extract

1 Answer 1

JSON requires double quotes. You can make this swap in the text using REPLACE().