How to handle dollar sign ($) in JSON path for super type in redshift?

user_7143036 0
Viewed 88

Summary:

I am trying to extract the values from a json stored as a super in redshift.

Context

This issue is near identical to the question posted here for TSQL. My schema:

user_id VARCHAR
properties SUPER

Sample data:

{
  "$os": "Mac OS X",
  "$browser": "Chrome",
  "token": "123x5"
}

I have this as a column in my table called properties.

Desired behavior

I want to be able to retrieve the value Mac OS X from the $os key and store it in a VARCHAR column.

What I've tried

I am able to retrieve the value for keys that do not have special characters in the following way: SELECT properties.token from clean

I have referenced the following aws docs:

Attempting to do the same

I have tried the following which haven't worked for me:

SELECT properties.'\$os' from clean

SELECT properties.'$os' from clean

SELECT properties[$os] from clean

SELECT properties['\$os'] from clean

Referencing the following docs: https://docs.aws.amazon.com/redshift/latest/dg/query-super.html#unnest I have also attempted to iterate over the super type using partisql:

select b.*
, pr
from base b, b.properties pr;

But this returns no rows.

I also tried the following:

select
    properties
    , properties.token
    , properties[0] praw0
    , properties[0].os os
    , properties[0][0] praw00
    , properties[0][0][0] praw000
from base

And this returned rows with value in the properties and token columns but nulls in all the other columns.

What am I missing? What else should I be trying?

amazon-redshift amazon-web-services special-characters sql json
1 Answers

You have to use double quotes ""

CREATE TEMP TABLE test_json
(
    user_id    VARCHAR,
    properties SUPER
);

INSERT INTO test_json VALUES (1,JSON_PARSE('{"$os": "Mac OS X", "$browser": "Chrome", "token": "123x5"}'));
SELECT properties."$os" from test_json

-- Output
"Mac OS X"
user_40462740
Related