Sqlalchemy json array , perform like query inside the array

Question

I have a table in my database that contains a column form which has JSONb datatype, and data most of the time is like this:

id = 1 
form = [{'label': 'x1', 'value': True},
 {'label': 'x2', 'value': 'v1'},
 {'label': 'x3', 'value': 'c2'}];
id = 2 
form = [{'label': 'x1', 'value': True},
 {'label': 'x2', 'value': 'v21'},
 {'label': 'x3', 'value': 'v22'}];

I want to know is that possible to search in that table to find rows that have text like c% in on elements in their form field? i.e. in the above case, I want to get entity with id =1;

It's not necessary to do that in sqlalchemy but if it's possible that is the preferred solution;

Jeremy · Accepted Answer · 2019-07-02 14:26:15Z

3

Yes, you can do this by breaking it apart:

select id 
from mytable
join lateral (select jsonb_array_elements(form) as elements) as sub1 on true
join lateral (select key, value from jsonb_each_text(elements)) as sub2 on true
WHERE key = 'value' and value like 'c%';
 id
----
  1

The first lateral join puts each item of the array into its own row. The second lateral join puts each key, value pair of the object into its own row. Then, we just look for rows where key = 'value' and value like 'c%'. If you also wanted to search for labels with value like 'c%' just remove the key = 'value' part.

EDIT: This will be easier in postgres 12 with json_path:

select id 
from mytable 
where jsonb_path_exists(form, '$.**.value ? (@ starts with "c")');

edited Jul 2, 2019 at 14:26

answered Jul 2, 2019 at 13:57

Jeremy

6,77322 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Sqlalchemy json array , perform like query inside the array

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