Reputation: 7526
I have an json column in a PrestoDB table mytable
. The rows contain varying amounts of key-value pairs - sometimes keys are present, and sometimes keys are absent, and sometimes the JSON contained in the row is wrapped in quotes, and sometimes it is not:
SELECT mycolumm from mytable
ORDER BY id DESC
LIMIT 3
{u'foo': 123, u'bar': u'abc', u'baz': 456}
{u'bar': u'abc'}
"{u'baz': 456}"
My objective is to get only the values of rows that contain the key baz
However, the following method does not work:
SELECT
JSON_EXTRACT_SCALAR(mycolumn, '$["baz"]')
from mytable
WHERE JSON_EXTRACT_SCALAR(mycolumn, '$["baz"]') IS NOT NULL
ORDER BY id
DESC
It should return
456
456
But the query returns nothing. What am I doing wrong?
Upvotes: 0
Views: 788
Reputation: 658
I think that your syntax is incorrect. Try this:
select
JSON_EXTRACT_SCALAR(mycolumn, '$.baz')
from mytable
WHERE JSON_EXTRACT_SCALAR(mycolumn, '$.baz') IS NOT NULL
ORDER BY ID DESC
You can see how json_path works here: https://github.com/json-path/JsonPath#path-examples. This is the notation that presto uses to navigate json strings.
Upvotes: 1