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

Question

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 ?

rahul.cs · Accepted Answer · 2021-09-13 07:54:04Z

1

Below Query worked on POSTGRES:

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

answered Sep 13, 2021 at 7:54

rahul.cs

552 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2021-09-12 18:27:22Z

0

If you are using SQL Server, you can use JSON_VALUE

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

answered Sep 12, 2021 at 18:27

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related