How to remove function name from the Postgres function result?

Question

I want to get the JSON response from the function response and I'm able to achieve that. But the problem is on every response I'm getting the function name also how to fix this issue?

Current response

[
    {
        "__find_attractions_return_json": {
            "name": "Conrad Pond",
            "lat": 41.01696,
            "lon": -74.22462
        }
    },
    {
        "__find_attractions_return_json": {
            "name": "Crossway Creek",
            "lat": 40.46122,
            "lon": -74.26292
        }
    },
    .....
    .....
]

Required result

[
    {
            "name": "Conrad Pond",
            "lat": 41.01696,
            "lon": -74.22462
    },
    {
            "name": "Crossway Creek",
            "lat": 40.46122,
            "lon": -74.26292
    },
    .....
    .....
]

POSTGRES function

CREATE OR REPLACE FUNCTION public.__find_attractions_return_json(point character varying, cty varchar)
    RETURNS SETOF json 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
BEGIN
  RETURN QUERY
SELECT 
    json_build_object( 
        'name',  name, 
        'lat', lat, 
        'lon', lon
        )
    FROM places
    WHERE country=cty 
    AND ST_DWithin(geom::geography,ST_GeogFromText(point), 50000, false);
END
$BODY$;

I'm calling this function from NodeJs.

Any kind of help will much be appreciated.

S-Man · Accepted Answer · 2019-07-24 09:30:11Z

1

demo:db<>fiddle

SELECT
    json_agg(elements -> '__find_attractions_return_json')
FROM 
    mytable,
    json_array_elements(mydata) as elements

json_array_elements expands all array elements into one row each
for every row getting the value of your removeable key
json_agg aggregates these value objects

answered Jul 24, 2019 at 9:30

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

How to remove function name from the Postgres function result?

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