Skip to content
Advertisement

How to use where clause referencing a column when querying a JSON object in another column in SQL

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'
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement