Postgres query on json with empty value

Question

I have a query in JSON to filter out the data based on data present inside JSON field .

Table name: audit_rules Column name: rule_config (json)

rule_config contains JSON which contain 'applicable_category' as an attribute in it.

Example

{
   "applicable_category":[
      {
         "status":"active",
         "supported":"yes",
         "expense_type":"Meal",
         "acceptable_variation":0.18,
         "minimum_value":25.0
      },
      {
         "status":"active",
         "supported":"yes",
         "expense_type":"Car Rental",
         "acceptable_variation":0.0,
         "minimum_value":25.0
      },
      {
         "status":"active",
         "supported":"yes",
         "expense_type":"Airfare",
         "acceptable_variation":0.0,
         "minimum_value":75
      },
      {
         "status":"active",
         "supported":"yes",
         "expense_type":"Hotel",
         "acceptable_variation":0.0,
         "minimum_value":75
      }
   ],
   "minimum_required_keys":[
      "amount",
      "date",
      "merchant",
      "location"
   ],
   "value":[
      0,
      0.5
   ]
}

But some of the rows doesn't have any data or doesn't have the 'applicable_category' attribute in it.

So while running following query i am getting error:

select s.*,j from 
  audit_rules  s 
   cross join lateral json_array_elements ( s.rule_config#>'{applicable_category}' ) as j
WHERE j->>'expense_type' in ('Direct Bill');

Error: SQL Error [22023]: ERROR: cannot call json_array_elements on a scalar

You tagged the question with three different, unsupported Postgres version (one of them not even supporting JSON). Which Postgres version are you really using? — user330315
– user330315, Commented Nov 10, 2020 at 7:33
PostgreSQL 9.6.18 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit — Rahul Anand
– Rahul Anand, Commented Nov 10, 2020 at 8:59

user330315 · Accepted Answer · 2020-11-10 07:40:52Z

2

You can restrict the result to only rows that contain an array:

select j.*
from audit_rules  s 
   cross join lateral json_array_elements(s.rule_config#>'{applicable_category}') as j
WHERE json_typeof(s.rule_config -> 'applicable_category') = 'array'
  and j ->> 'expense_type' in ('Meal')

answered Nov 10, 2020 at 7:40

user330315

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

Collectives™ on Stack Overflow

Postgres query on json with empty value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related