无法根据SQL Server中另一列的条件检索列中的不同行

由 发布于

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}}

我想要的输出如下：

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

基本上，我想要在JsonObject列中遵循PricingRule作为Rule1的唯一ID。 我尝试了以下代码：

select Id, JsonObject  from my_table 
where json_value(JsonObject, '$.Pricing.PricingRule') = 'Rule1'