Get a JSON from an SQL query in Postgres [duplicated]

2

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.

    
asked by Jonathan 20.12.2018 в 18:12
source

1 answer

3

There are functions that can be added in JSON format:

json_agg(value) creating an array,

json_object_agg(name, value) created by the object with the added attributes:

select json_object_agg(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
    
answered by 20.12.2018 / 18:24
source