Postgres json_agg order by

Question

I have multiple tables in a json_agg for psql:

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 < 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;

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:

ERROR: missing FROM-clause entry for table "farm_location" LINE 6: ...FROM supply_forecast WHERE delete = B'0' ORDER BY farm_locat... ^
********** Error **********

ERROR: missing FROM-clause entry for table "farm_location"
SQL state: 42P01
Character: 642

If I remove the ORDER BY farm_location.distance desc the query works but the data is not sorted by distance. Any idea on how to go about this?

Ed Bangga · Accepted Answer · 2019-11-26 06:24:04Z

6

json_agg function can support order by parameter.

SELECT json_agg(t.* ORDER BY distance desc) 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 < 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' 
) t;

answered Nov 26, 2019 at 6:24

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

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

6 Comments

Arnold Cristobal Over a year ago

"ERROR: column "distance" does not exist" when I try this query. :(

Ed Bangga Over a year ago

change first to t.*, then check the column names.

Ed Bangga Over a year ago

or put this under b order by b.distance desc

Arnold Cristobal Over a year ago

Checked and the distance field exist "distance": 0.000493328699954841 but placing the order by in t or b or adding b.distance still throws error "ERROR: missing FROM-clause entry for table "b""

Ed Bangga Over a year ago

@ArnoldCristobal, how about inside b? after distance < 100

|

bayronantoniocm · Accepted Answer · 2021-04-09 17:05:53Z

I apply ORDER BY inside function json_agg() and itś work OK

select
    jsonb_agg(jsonb_build_object('toma_parada', app51.app51_tomas__toma_parada)|| jsonb_build_object('lado', app51.app51_tomas__lado)|| jsonb_build_object('existen_tomas_paradas', app51.app51_tomas__existen_tomas_paradas)|| jsonb_build_object('b', app51.app51_tomas__b)|| jsonb_build_object('a', app51.app51_tomas__a)|| jsonb_build_object('m', app51.app51_tomas__m)|| jsonb_build_object('nombre', app51.app51_tomas__nombre, 'id_publico', app51.app51_tomas__id_publico , 'id', app51.app51_tomas__id ) 
    order by app51.app51_tomas__id desc) as app51_tomas,
    app51.fecha_modificacion,
    app51.fecha_creacion,
    app51.id_cliente,
    app51.id_rumas,
    app51.id_usuario,
    app51.id,
    app51.id_publico,
    app51.habilitado,
    app51.borrado,
    app51.produccion_fin,
    app51.produccion_inicio
from
    (
    select
        distinct app51.id_cliente,
        app51.id_rumas,
        app51.id_usuario,
        app51.id,
        app51.id_publico,
        app51.habilitado,
        app51.borrado,
        app51.atributos ->> 'produccion_fin' as produccion_fin,
        app51.atributos ->> 'produccion_inicio' as produccion_inicio,
        app51_tomas.atributos ->> 'toma_parada' as app51_tomas__toma_parada,
        app51_tomas.atributos ->> 'lado' as app51_tomas__lado,
        app51_tomas.atributos ->> 'existen_tomas_paradas' as app51_tomas__existen_tomas_paradas,
        app51_tomas.atributos ->> 'b' as app51_tomas__b,
        app51_tomas.atributos ->> 'a' as app51_tomas__a,
        app51_tomas.atributos ->> 'm' as app51_tomas__m,
        app51_tomas.atributos ->> 'nombre' as app51_tomas__nombre,
        app51_tomas.id as app51_tomas__id ,
        app51_tomas.id_publico as app51_tomas__id_publico ,
        jsonb_build_object() as id_usuario_creacion,
        jsonb_build_object() as id_usuario_modificacion,
        (
        select
            TO_CHAR(app51.fecha_creacion::timestamptz at time zone 'GMT' , 'YYYY-MM-DD"T"HH24:MI:SS.ss0"Z"')) as fecha_creacion ,
        (
        select
            TO_CHAR(app51.fecha_modificacion::timestamptz at time zone 'GMT' , 'YYYY-MM-DD"T"HH24:MI:SS.ss0"Z"')) as fecha_modificacion
    from
        app51
    left join app51_app51_tomas on
        app51_app51_tomas.id_app51 = app51.id
    left join app51_tomas on
        app51_app51_tomas.id_app51_tomas = app51_tomas.id
        and ((app51_tomas.habilitado = true
        and app51_tomas.borrado = false)
        or app51_tomas is null )
    where
        (app51.habilitado = true
        and app51.borrado = false)
    order by
        app51_tomas.id desc) as app51
group by
    app51.fecha_modificacion,
    app51.fecha_creacion,
    app51.id_cliente,
    app51.id_rumas,
    app51.id_usuario,
    app51.id,
    app51.id_publico,
    app51.habilitado,
    app51.borrado,
    app51.produccion_fin,
    app51.produccion_inicio

Collectives™ on Stack Overflow

Postgres json_agg order by

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related