title: Lateral Join in django queryset (in order to use jsonb_to_recordset postgresql function)
body: <p>I have a model "myModel" saving some data in a (postgresql) jsonField named "json",
  The typical structure of the json data is:
  {key:[{"a":1, "b":2}, {"a":3, "b":4}]}.</p>
  
  <p>I would like to filter myModel queryset according to the values of "a" or "b".
  I may also want to aggregate over "a" or "b"</p>
  
  <p>So "unnesting" the (json -> key) array would be very appreciated,
  but I can't figure out how to do this with django api.</p>
  
  <p>I have try to perform the "unnesting" straight in postgresql via the following SQL query. </p>
  
  <pre><code>SELECT * 
  FROM "myModel"
  join lateral jsonb_to_recordset("myModel"."json" -&gt; 'key') as r("a" int, "b" int) on true
  LIMIT 5
  </code></pre>
  
  <p>We can even make it more compact using shortcut notation for the lateral join</p>
  
  <pre><code>SELECT * 
  FROM "myModel", jsonb_to_recordset("myModel"."json" -&gt; 'key') as r("a" int, "b" int)
  LIMIT 5
  </code></pre>
  
  <p>But I have no idea how to do something equivalent using the django API.
  I've tried a few things with annotate and RawSQL, but not of them seems to act on the "FROM" clause.  Which is the place where I should actually add the 'jsonb_to_recordset' statement.
  I could probably use the raw function to place my raw SQL, but that would mean I can't "filter" or "aggregate" on the joined quesryset using the django API.... I'd have to do everything in the rawSQL which is not very convenient for what I have to do.</p>
  
  <p>Another approach, would be to use the queryset "extra" function that allows to add an additional table in the sql FROM clause.
  Unfortunately, if I do:</p>
  
  <pre><code>qs = myModel.objects.all()
  qs = qs.extra(tables = ["""jsonb_to_recordset("myApp_myModel"."json" -&gt; 'key') as r("a" int, "b" int)"""])
  qs = qs.values()
  print(qs.query)
  </code></pre>
  
  <p>I get the query django would execute:</p>
  
  <pre><code>SELECT * 
  FROM "myModel", "jsonb_to_recordset("myModel"."json" -&gt; 'key') as r("a" int, "b" int)"
  </code></pre>
  
  <p>That's pretty close to what I need... except that django has added extra quotes around the extra "table" name I provided...
  So the function doesn't work anymore.</p>
  
  <p>Any idea of how to handle this?</p>
  
  <p>Thanks in advance,
  Loic</p>