Postgresql – Create multi-key JSON object with integers or floats as values

jsonpostgresql

I have a table of text and double precision type. From it, I want to build a JSON object that looks like this: {"a": 1.0, "b": 1.2, "c": 3.4} (edit: to clarify, the keys are the text column in my table, and the values are the double precision column). Note that there are no quotes around the values because they are floats, not strings, which the JSON standard supports.

Using Postgres 9.5. There's the json_build_object function that takes two parallel text arrays and builds a JSON object with text keys and text values… also works with NULL values, turning them into null (no quotes) in JSON. So I'd get {"a": "1.0", "b": "1.2", "c": "3.4"}. But what if I want integer or float values? It won't take any numeric type arrays.

I got around it for now by making my own function to handle this in Python! Is there a way to do this without defining a new function?

CREATE OR REPLACE FUNCTION json_build_object_float_values(keys TEXT[], vals DOUBLE PRECISION[]) RETURNS JSONB AS
$$
    assert len(keys) == len(vals)
    import json
    d = {}
    for i in range(len(keys)):
        d[keys[i]] = float(vals[i])
    return json.dumps(d)
$$
LANGUAGE 'plpython3u' IMMUTABLE;

(note if you use this, it returns JSONB, but you could change it to JSON)

Edit, here's test input and expected output:

CREATE TABLE test (str text, num double precision);
INSERT INTO test(str, num) VALUES ('a', 1.0);
INSERT INTO test(str, num) VALUES ('b', 2.3);
-- Here's my solution using my custom function...
SELECT json_build_object_float_values(array_agg(str), array_agg(num)) FROM test;
-- expected output: 
--  json_build_object_float_values
-- --------------------------------
--  {"a": 1.0, "b": 2.3}
-- (1 row)

Simpler one:

SELECT json_build_object_float_values(ARRAY['the', 'quick'], ARRAY[1, 3]);

 json_build_object_float_values
--------------------------------
 {"the": 1.0, "quick": 3.0}
(1 row)

Best Answer

Update

According to your sample data and desired result you can get it using json_object_agg Postgres function.

CREATE TABLE test (str text, num double precision);
INSERT INTO test(str, num) VALUES ('a', 1.0);
INSERT INTO test(str, num) VALUES ('b', 2.3);

select json_object_agg(str, num)
from test;

| json_object_agg        |
| :--------------------- |
| { "a" : 1, "b" : 2.3 } |

dbfiddle here

You can use row_to_json Postgres function.

create table foo(f1 int, f2 varchar(10), f3 float, f4 decimal(18,2));
insert into foo values (2, 'name', 2.345, 23.23);

select row_to_json(f)
from   (select f1,f2,f3,f4 from foo) f;

| row_to_json                                |
| :----------------------------------------- |
| {"f1":2,"f2":"name","f3":2.345,"f4":23.23} |

dbfiddle here

Related Question