Could not able to retrieve distinct rows from a Column based on the condition of another column in SQL server

user_13502046 0
Viewed 44

I am new to SQL and I have a big table my_table having thousands of rows. The following is a sample:

 Id           JsonObject
  1           {"Pricing":{"PricingRule":Rule1}}
  1           {"Pricing":{"PricingRule":Rule2}}
  2           {"Pricing":{"PricingRule":Rule1}}
  3           {"Pricing":{"PricingRule":Rule4}}       
  3           {"Pricing":{"PricingRule":Rule1}}
  4           {"Pricing":{"PricingRule":Rule1}}
  4           {"Pricing":{"PricingRule":Rule9}}
  5           {"Pricing":{"PricingRule":Rule1}}
  6           {"Pricing":{"PricingRule":Rule10}}

What I want in the output is following:

 Id              JsonObject
  2              {"Pricing":{"PricingRule":Rule1}}
  5              {"Pricing":{"PricingRule":Rule1}}

Basically, I want the unique Ids which follow PricingRule as Rule1 from the JsonObject column. I tried the following code:

select Id, JsonObject  
from my_table 
where json_value(JsonObject, '$.Pricing.PricingRule') = 'Rule1'
sql-server sql json
1 Answers

Try the following, here is the demo.

select
  Id,
  JsonObject
from
(
  select 
    *,
    count(*) over (partition by id) as total
  from Table1 
) val
where json_value(JsonObject, '$.Pricing.PricingRule') = 'Rule1'
and total = 1

Output:

*-----------------------------------------*
| Id      JsonObject                      |
*-----------------------------------------*
| 2   {"Pricing":{"PricingRule":"Rule1"}} |
| 5   {"Pricing":{"PricingRule":"Rule1"}} |
*-----------------------------------------*
user_86913190
Related