Parse Key-Value pair column stored as Varchar-CodePudding

I am attempting to query a column that has data stored in key-value pair. I thought it was JSON but after checking the properties it appears it's a varchar column. I am not quite sure how to query such a column. It looks something like:

{ "Employee":"John", "EmployeeID":"1", "Role":"Marketing" }

There are some nested pairs in there as well. Any help around how to extract values from this type of JSON looking column (it's of type varchar) will be very much appreciated.

CodePudding user response：

If it's indeed valid JSON, just cast it to data type json (or jsonb for certain operations) and treat it as such, using Postgres' arsenal of JSON functionality. Like:

SELECT col::json->>'Role' AS the_role
FROM   tbl;

db<>fiddle here

Of course, in a sane world, one would work with data type json or jsonb in the table to begin with.

CodePudding user response：

It looks like it was a Redshift problem as it offers limited support to work with JSON files. I was able to query the key-value pair by using the following function: JSON_EXTRACT_PATH_TEXT()

Select *, 
json_extract_path_text(col, 'Employee', true) as Employee,
json_extract_path_text(col, 'EmployeeID', true) as EmployeeID,
json_extract_path_text(col, 'Role', true) as Role
from table;

Here's some documentation:

https://sonra.io/redshift/working-with-json-in-redshift-options-limitations-and-alternatives/#:~:text=Redshift does not have a,SQL functions offered in Redshift. https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html Querying JSON fields in Redshift