title: Postgres json_agg order by
body: <p>I have multiple tables in a <code>json_agg</code> for <code>psql</code>:</p>
  
  <pre><code>SELECT json_agg(t) FROM (SELECT *, 
      ( SELECT row_to_json(b) FROM ( SELECT * from (SELECT *, ( (3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) * 1.609344 ) AS distance
      from farm_location ) al WHERE farm_location_id=supply_forecast.farm_location_id and distance &lt; 100 ) b) as farm_location, 
      ( SELECT json_agg(c) FROM ( SELECT * 
      FROM supply_forecast_status_history WHERE supply_forecast_id=supply_forecast.supply_forecast_id) c) as supply_forecast_status
  FROM supply_forecast WHERE delete = B'0' ORDER BY farm_location.distance desc) t;
  </code></pre>
  
  <p>I'm trying to calculate the distance from a farm location.  The issue is I'm trying to sort the data by "distance" but it's throwing an error:</p>
  
  <blockquote>
    <p>ERROR:  missing FROM-clause entry for table "farm_location"
        LINE 6: ...FROM supply_forecast WHERE delete = B'0' ORDER BY farm_locat...
                                                                 ^</p>
  
  <pre><code>********** Error **********
  
  ERROR: missing FROM-clause entry for table "farm_location"
  SQL state: 42P01
  Character: 642
  </code></pre>
  </blockquote>
  
  <p>If I remove the <code>ORDER BY farm_location.distance desc</code> the query works but the data is not sorted by distance.  Any idea on how to go about this?</p>