I have the following sales table with a nested JSON object:
sale_id | sale_date | identities |
---|---|---|
41acdd9c-2e86-4e84-9064-28a98aadf834 | 2017-05-13 | {“SaleIdentifiers”: [{“type”: “ROM”, “Classifier”: “CORNXP21RTN”}]} |
To query the Classifier I do the following:
select Classifier from (select identities.SaleIdentifiers.Classifier FROM sales ) as Classifiers
This gives me the result:
Classifier |
---|
CORNXP21RTN |
How would I go about using the sale_date
column in a where clause? For instance this shows me a list of the classifiers in a specific sale_date
:
select Classifier from (select identities.SaleIdentifiers.Classifier FROM sales ) as Classifiers where sale_date = '2017-05-13'
Advertisement
Answer
No need for subquery:
select identities.SaleIdentifiers.Classifier as Classifier FROM sales where sale_date = '2017-05-13'