3

I need to find how to build a query with django ORM I have two tables with a relation one to many.

table A
id = integer autonumeric
data = postgres json
...

table B
id = integer autonumeric
id_A = table A id
name = string
t_name = string like slug 

the A.data have a json with the structure {key (t_name of table B): value (is a string value)}

so that the definition of entities (name and t_name) are in the table B and the values of that entities ere in the json structure in the table A.

eje:

   Table A
   ------------
   id | data
   1  |{"a_01":"value a","a_02":"value a","b_01":"value b"}
   2  |{"a_01":"value a","b_01":"value b"}

   Table B
   -----------
   id | id_A | name | t_name
   1  |   1  |   A  | a_01 
   2  |   1  |   AA | a_02
   3  |   1  |   B  | b_01
   4  |   2  |   A  | a_01
   5  |   2  |   B  | b_01

the id_A and t_name are uniques together

I need to get the items from table A with name (B.name) and value (A.data."t_name") from django orm

this query solve my problem but I don't how do in django ORM

SELECT at.id, at.data, bt.name
FROM "A" AS at
JOIN "B" AS bt ON at.id=bt.id_a
WHERE data ->> bt.t_name = 'value a' AND bt.name='AA'
LIMIT 50;

the result is:

1 | {"a_01":"value a","a_02":"value a","b_01":"value b"} | AA
Oka
  • 101
  • 6

0 Answers0