Amazon Web Services Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – AWS Redshift SQL – How to check if a Json object has values in it?

1/ I am using SQL on AWS and trying to detect quickly when a Json object has key-value in it. Anyone knows it? Thanks

For example:
A. {"context":{"abc":"123"} –> Yes

B. {"context":{}} –> No

Note that I don’t know in advance the keys in this case.

2/ Also is it possible to collect all the keys?

Thank you.

Tags:

2

Answers


  1. 0

    I assume you are using AWS Athena, you can use json_extract to parse the json string and then use json_length to check if the json object number of keys is more than 0

    SELECT
  dataset.name, dataset.data
FROM (
  SELECT 'A' AS name, '{"context":{"abc":"123"}}' AS data
  UNION ALL 
  SELECT 'B' , '{"context":{}}'
) as dataset
where json_length(json_extract(dataset.data, '$.context')) > 0
    Login or Signup to reply.
  2. 0

    In Redshift the easiest way to work with json data is as a super data type column. You can do this as json text but it isn’t as powerful. See: https://docs.aws.amazon.com/redshift/latest/dg/query-super.html

    There are a number of ways to do what you want. Here’s a couple:

    with foo as (
  SELECT 'A' AS name, json_parse('{"context":{"abc":"123"}}') AS data
  UNION ALL 
  SELECT 'B' , json_parse('{"context":{}}')
)
select name, data.context, json_size(data.context), data.context.abc
from foo;

    As for your #2 objective this can be done by unpivoting the object in the super you want to get the keys for. Like this:

    with foo as (
  SELECT 'A' AS name, json_parse('{"context":{"abc":"123", "def":"456"}}') AS data
  UNION ALL 
  SELECT 'B' , json_parse('{"context":{}}')
)
select name, data.context, key
from foo as f, unpivot f.data.context as value at key;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search