Formatting JSON table from Postgresql request

Question

I'm trying to create a Json format from a postgresql request.

Firstly I have used Rails to request my database in the format.json block of my controller and then used a json.builder file to format the json view. It worked until my requests return hundreds of thousands rows, so I searched how to optimize the json creation, avoiding all the ActiveRecord stack.

To do this I am using Postgresql 9.6 json functions to get directly my data in the right format, which is for example :

SELECT array_to_json('{{1157241840,-1.95},{1157241960,-1.96}}'::float[]);
[[1157241840, -1.95], [1157241960, -1.96]]

But using data from this kind of request :

SELECT date,value FROM measures;

The best I could obtain was something like this :

SELECT array_to_json(array_agg(t)) FROM (SELECT date,value FROM measures) t;

Resulting in :

[
 {"date":"1997-06-13T19:12:00","value":1608.4},
 {"date":"1997-06-13T19:12:00","value":-0.6}
]

which is quite different ... How would you build this SQL request ?
Thanks for your help !

My measures table look like this :

      id | value |          created_at        |         updated_at         | parameter_id | quality_id | station_id |          date       | campain_id | elevation | sensor_id | comment_id  
 --------+-------+----------------------------+----------------------------+--------------+------------+------------+---------------------+------------+-----------+-----------+------------
  799634 | -1.99 | 2017-02-21 09:41:09.062795 | 2017-02-21 09:41:09.118807 |            2 |            |          1 | 2006-06-26 23:24:00 |          1 |      -5.0 |           |
 1227314 | -1.59 | 2017-02-21 09:44:12.032576 | 2017-02-21 09:44:12.088311 |            2 |            |          1 | 2006-11-30 19:48:00 |          1 |      -5.0 |           |           
 1227315 | 26.65 | 2017-02-21 09:44:12.032576 | 2017-02-21 09:44:12.088311 |            3 |            |          1 | 2006-11-30 19:48:00 |          1 |      -5.0 |           |

id | value | created_at | updated_at | parameter_id | quality_id | station_id | date | campain_id | elevation | sensor_id | comment_id 799634 | -1.99 | 2017-02-21 09:41:09.062795 | 2017-02-21 09:41:09.118807 | 2 | | 1 | 2006-06-26 23:24:00 | 1 | -5.0 | | — B.Coatanéa
– B.Coatanéa, Commented Feb 22, 2017 at 9:41
Sorry for the bad indentation, I have not found how to break a line in comments. The header of a measure stop at "comment_id" and the sample starts at 799634. — B.Coatanéa
– B.Coatanéa, Commented Feb 22, 2017 at 10:01
please, update your question with fields, not comment. also when I run your example, I get error: t=# SELECT array_to_json('{{1157241840,-1.95},{1157241960,-1.96}}'::int[]); ERROR: invalid input syntax for integer: "-1.95" LINE 1: SELECT array_to_json('{{1157241840,-1.95},{1157241960,-1.96}... — Vao Tsun
– Vao Tsun, Commented Feb 22, 2017 at 10:03
Indeed, it's not int[] but float[] that I meant. I edited my question. — B.Coatanéa
– B.Coatanéa, Commented Feb 22, 2017 at 11:19

Roman Tkachuk · Accepted Answer · 2017-02-22 10:32:06Z

1

If you need array of array you need to use json_build_array:

SELECT json_agg(json_build_array(date,value)) FROM measures;

If you want convert timestamp to epoch:

SELECT json_agg(json_build_array(extract(epoch FROM date)::int8, value)) FROM measures;

For test:

WITH measures AS (
    SELECT 1157241840 as date, -1.95 as value
     UNION SELECT 1157241960, -1.96
     UNION SELECT 1157241980, NULL
) 


SELECT json_agg(json_build_array(date,value)) FROM measures;


                            json_agg                            
----------------------------------------------------------------
 [[1157241840, -1.95], [1157241960, -1.96], [1157241980, null]]

edited Feb 22, 2017 at 10:32

answered Feb 21, 2017 at 15:56

Roman Tkachuk

3,2761 gold badge18 silver badges16 bronze badges

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

3 Comments

B.Coatanéa Over a year ago

The test is working for me, the json_build_array or jsonb_build_array with my database also. But when I try to build the array of array with json_agg or array_agg I just have an empty result.

Roman Tkachuk Over a year ago

Could you try json_agg for some data from your table? Try to do it with one row.

B.Coatanéa Over a year ago

Thanks. That was indeed a problem with the json_agg format, the less output on my terminal was not showing the result and I don't know why. By making the test for just one row I have noticed that.

Clodoaldo Neto · Accepted Answer · 2017-02-22 12:05:27Z

0

create table measures (date timestamp, value float);
insert into measures (date, value) values
(to_timestamp(1157241840),-1.95),
(to_timestamp(1157241960),-1.96);

select array_to_json(array_agg(array[extract(epoch from date), value]::float[]))
from measures
;
              array_to_json              
-----------------------------------------
 [[1157241840,-1.95],[1157241960,-1.96]]

answered Feb 22, 2017 at 12:05

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Formatting JSON table from Postgresql request

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related