2

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)
  • json_build_object looked promising, but it takes a variadic parameter list, not two arrays. Not sure if I can use that. – sudo Apr 17 '17 at 21:27
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.