CODEWITHSUNDEEP
sqljsonpostgresql-9.5
Alexei
Alexei

Reputation: 15674

Is it possible to compare different fields in jsonb

Table with column type jsonb.

Here json

"weight": {
    "qty": 1112,
    "unit": {
      "name": "gram"
    }
  },
  "options": [
    {
      "name": "genus_species",
      "value": {
        "name": "cucumber"
      }
    },
    {
      "name": "weight",
      "value": {
        "name": "600g"
      }
    },
    {
      "name": "method",
      "value": {
        "name": "salt"
        }
      }
    },
    {
      "name": "tm",
      "value": {
        "name": "chudova-marka"
      }
    }
  ]

In this example "weight.qty" with value "1112" not equal with "value.name.600g". So this record will be return.

P.S. I convert "qty" to string and add suffix "g" to can compare as strings.

So here query without compare

SELECT 
concat(product.data->'weight'->>'qty', 'g') AS weight,
product.data #>>'{options,0,value,name}' as n1,
product.data #>>'{options,1,value,name}' as n2,
product.data #>>'{options,2,value,name}' as n3,
product.data #>>'{options,3,value,name}' as n4
FROM product

I need to show records where "weight.qty" not equals with any "options.value.name". Is it possible?

Upvotes: 0

Views: 28

Answers (1)

user330315
user330315

Reputation:

You need to unnest the array elements:

select p.*
from product p
where not exists (select * 
                  from jsonb_array_elements(p.data -> 'options') as x(option)
                  where x.option ->> 'name' = 'weight' 
                    and x.option -> 'value' ->> 'name' = concat(p.data->'weight'->>'qty', 'g'))

Upvotes: 1

Related Questions