I have several tables with the following data:
| sonda | fecha | valor |
+-------+-------------------------+--------+
| s1 | 2018-10-23 23:34:00.444 | 34.5 |
| s2 | 2018-10-23 23:34:00.444 | 4.5 |
| s3 | 2018-10-23 23:34:00.444 | 10.5 |
| s4 | 2018-10-23 23:34:00.444 | 5 |
| s1 | 2018-10-23 23:35:00.444 | 23.5 |
| s2 | 2018-10-23 23:35:00.444 | 44.5 |
| s3 | 2018-10-23 23:35:00.444 | 6.7 |
| s4 | 2018-10-23 23:35:00.444 | 13.4 |
And I execute the following SQL query:
select json_build_object(datetime, value) as mDatos
from (
select
extract(epoch from (datetime::timestamp))::numeric::integer as datetime,
value
from data_1_4_2018_12
where resource_id='s2'
)datos
With what I get:
| mDatos |
+------------------------------+
| {"1543837080":4.5} |
| {"1543837120":44.5} |
And what I would like to obtain is:
mDatos
-----------------------------------------
{
"1543837080":4.5,
"1543837080":4.5
}
Right now I am focusing on the data part but I want to get a much more complete JSON. This is the part that I lack. The idea is to finally create a JSON like the following after consulting other tables:
{
"etiqueta":"Sonda temperatura habitación 1",
"nombre":"s2",
"datos":
{
"1543837080":4.5,
"1543837120":44.5
}
},
{
"etiqueta":"Sonda temperatura habitación 3",
"nombre":"s3",
"datos":
{
"1543837080":10.5,
"1543837120":6.7,
....
....
}
},
{
....
}
Can you help me out with the SQL query for Postgres 9.4?
There is a "like" question already asked: How can I make a sql that contains an array of objects and that those objects contain 2 column data? I missed the "duplicate" question message "But I wanted to clarify why I think they are different. My problem is that I did not know how to create a json object whose "key-value" values were the records in the table. In that question they wanted to know how to obtain an array of json objects.