Returning array of JSONs by PostgreSQL

Question

I am trying to get array of JSONs via using query below. But the result is array of tuples. How can I get rid of parantheses which covers each dictionary element and make the result array of dictionaries?

SELECT row_to_json(foo)
FROM
(
    SELECT ilce, mah, phone 
    FROM ilan 
    LIMIT 5
) as foo;

Query above generates the first result. How can I get the second one?

First Result:

[({'ilce': 'esenyurt', 'mah': 'mehmet akif ersoy mh.', 'phone': '05308635664'},), ({'ilce': 'esenyurt', 'mah': 'mehmet akif ersoy mh.', 'phone': '05417411067'},), ({'ilce': 'esenyurt', 'mah': 'mehmet akif ersoy mh.', 'phone': '05536253236'},), ({'ilce': 'esenyurt', 'mah': 'mehmet akif ersoy mh.', 'phone': '05308776153'},), ({'ilce': 'esenyurt', 'mah': 'mehmet akif ersoy mh.', 'phone': '05308635664'},)]

Second one:

[{'ilce': 'esenyurt', 'mah': 'mehmet akif ersoy mh.', 'phone': '05308635664'}, {'ilce': 'esenyurt', 'mah': 'mehmet akif ersoy mh.', 'phone': '05417411067'}, {'ilce': 'esenyurt', 'mah': 'mehmet akif ersoy mh.', 'phone': '05536253236'}, {'ilce': 'esenyurt', 'mah': 'mehmet akif ersoy mh.', 'phone': '05308776153'}, {'ilce': 'esenyurt', 'mah': 'mehmet akif ersoy mh.', 'phone': '05308635664'}]

score 1 · Accepted Answer · 2020-06-04 10:55:11Z

1

Use jsonb_agg() and to_jsonb()

SELECT jsonb_agg(to_jsonb(foo))
FROM
(
    SELECT ilce, mah, phone 
    FROM ilan 
    LIMIT 5
) as foo;

Online example

edited Jun 4, 2020 at 10:55

answered Jun 4, 2020 at 10:45

user330315

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

2 Comments

mcan Over a year ago

Thanks. But need a little tweaking. Now the result is

[([{'mah': 'mehmet akif ersoy mh.', 'ilce': 'esenyurt', 'phone': '05308635664'}, {'mah': 'mehmet akif ersoy mh.', 'ilce': 'esenyurt', 'phone': '05417411067'}, {'mah': 'mehmet akif ersoy mh.', 'ilce': 'esenyurt', 'phone': '05536253236'}, {'mah': 'mehmet akif ersoy mh.', 'ilce': 'esenyurt', 'phone': '05308776153'}, {'mah': 'mehmet akif ersoy mh.', 'ilce': 'esenyurt', 'phone': '05308635664'}],)]

It can be get by result[0][0][actual index]. But how can I get rid of these first two zero indexes.

user330315 Over a year ago

This query does exactly what you want: dbfiddle.uk/… if you get a different result there must be something else involved which you are not telling us

Collectives™ on Stack Overflow

Returning array of JSONs by PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related