JSONB output from object.query() results in syntax error on raw sql

Question

I am quite new to django and JSONB and I use this following syntax to execute a search on JSONB data fields:

obj=SomeModel.objects.filter(data__0__fieldX__contains=search_term)

.. and it works as intended. Now, I print out the obj.query for the above statement and I get:

SELECT * FROM "somemodel_some_model" 
WHERE ("somemodel_some_model"."data"
#> ['0', 'fieldX']) @> '"some lane"'

However, when I excecute the above using:

obj=SomeModel.objects.raw(`query statement above`)

I get an error:

django.db.utils.ProgrammingError: syntax error at or near "["
LINE 3:         #> ['0', 'fieldX']) @> '"some lane"'

I presume I am not escaping the "[", and I have tried using a backslash before, but it does not seem to help.

Vao Tsun · Accepted Answer · 2018-02-13 09:04:50Z

0

what you do is smth like:

with c(j) as (values('
[
  {
    "fieldX": -20,
    "fieldY": 40
  },
  {
    "fieldX":10,
    "fieldY": 0
  }
]
'::jsonb))
select j #> ['0', 'fieldX'] from c;
ERROR:  syntax error at or near "["
LINE 13: select j #> ['0', 'fieldX'] from c;

what you have to do is smth like:

t=# with c(j) as (values('
[
  {
    "fieldX": -20,
    "fieldY": 40
  },
  {
    "fieldX":10,
    "fieldY": 0
  }
]
'::jsonb))
select j #> '{0,fieldX}' from c;
 ?column?
----------
 -20
(1 row)

https://www.postgresql.org/docs/9.5/static/functions-json.html

text[] is array, but in Postgres array are presented as '{}' or array[], not just []

so

 j #> array[0,'fieldX']::text[] from c

would also work

answered Feb 13, 2018 at 9:04

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

3 Comments

AJW Over a year ago

Thanks a tonne for this @Vao - REALLY helps. I have seemed to solve that problem now. I have shamelessly edited the question to ask for another, where, when I use the same logic on a case insensensitive lookup, I get a syntax error near %. It just seems really odd, and I wonder if it is somehow related.

Vao Tsun Over a year ago

please don't mix posts. just ask a new question

AJW Over a year ago

I have now moved it to a new question here and accepted your answer on this: stackoverflow.com/questions/48764138/…

Collectives™ on Stack Overflow

JSONB output from object.query() results in syntax error on raw sql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related