CODEWITHSUNDEEP
sqlpostgresql
rahul.cs
rahul.cs

Reputation: 55

How to find count of specific column from JSON data using SQL query

I have a column which stores data in JSON format. Now I need to find count of specific attribute from all rows using same column.

Column Name: LogDetails
Data Format : {"ORDER_STATUS":"SUCCESS","ORDER_TYPE":"ODSP","ORDER_DATE":"27/03/2021","ORDER_POLLING":"\nCOUNTRY : RUSSIA, PO_ACCEPTED_STATE : Success"}

Here I need to find count where all ORDER_STATUS is SUCCESS.

How can we process it using SQL ?

Upvotes: 1

Views: 1300

Answers (2)

rahul.cs
rahul.cs

Reputation: 55

Below Query worked on POSTGRES:

SELECT COUNT(*)
FROM table
WHERE ORDER_STATUS::json->>'ORDER_STATUS' = 'SUCCESS'

Upvotes: 1

Charlieface
Charlieface

Reputation: 71579

If you are using SQL Server, you can use JSON_VALUE

SELECT COUNT(*)
FROM YourTable t
WHERE JSON_VALUE(t.LogDetails, '$.ORDER_STATUS') = 'SUCCESS';

Upvotes: 0

Related Questions