Compare numeric values inside a jsonb field of postgres

Question

I have a column inside a postgresql database table of type jsonb called meta. This is the data i have inside one of the rows in the meta column of the table.

{
    budget: {
        data: "2018-12-15",
        target: 47.5,
        spend: 12.3
    } 
}

I am trying to write a query that return all rows where

meta.budget.spend < meta.budget.target 

or

meta.budget.date != "2018-12-15"

I tried

SELECT ... WHERE (("table"."meta"#>>'{budget,spend}') < "table"."meta"#>>'{budget,target}'
       OR ("table"."meta"#>>'{budget,date}') != '2018-12-15')

and i got 0 row as a result. I'm I doing the query right? If not, how do i fix it.

Answer 1

You could use:

SELECT *
  ,(meta->'budget'->'target')::text::numeric(10,2) AS target
  ,(meta->'budget'->'spend')::text::numeric(10,2) AS spend
FROM tab
WHERE (meta->'budget'->'spend')::text::numeric(10,2) < 
      (meta->'budget'->'target')::text::numeric(10,2)
   OR (meta->'budget'->'data')::text::date != '2018-12-15'::date

db<>fiddle demo

Answer 2

Your problem is that #>> gives you text:

Operator: #>>
Right Operand Type: text[]
Get JSON object at specified path as text

and text won't necessarily compare the way you want. If you add a couple type casts, you'll get better results:

   ("table"."meta" #>> '{budget,spend}')::numeric < ("table"."meta" #>> '{budget,target}')::numeric
OR ("table"."meta" #>> '{budget,date}')::date != '2018-12-15'

You could also say '2018-12-15'::date if you like to be explicit but that's implied by the date on the left hand side.