0

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.

Bubunyo Nyavor
  • 2,511
  • 24
  • 36

2 Answers2

0

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

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • when i do this i this is the error i get `SequelizeDatabaseError: operator does not exist: text < numeric` – Bubunyo Nyavor Dec 15 '18 at 19:24
  • I think you're missing the `::numeric` typecast on the left side, see https://dbfiddle.uk/?rdbms=postgres_10&fiddle=afab6da2591fb4d469ef2e873080271c for an example. – mu is too short Dec 15 '18 at 20:10