How to convert two rows into key-value json object in postgresql?

Use the aggregate function json_object_agg(key, value):

select     name,     json_object_agg(key, value) as datafrom datajoin meta on fk_id = idgroup by 1;

SQLFiddle.

The function was introduced in Postgres 9.4.

I found a way to return crosstab data with dynamic columns. Maybe rewriting this will be better to suit your needs:

CREATE OR REPLACE FUNCTION report.usp_pivot_query_amount_generate(    i_group_id INT[],    i_start_date TIMESTAMPTZ,    i_end_date TIMESTAMPTZ,    i_interval INT    ) RETURNS TABLE (    tab TEXT    ) AS $ab$DECLARE    _key_id TEXT;    _text_op TEXT = '';    _ret TEXT;BEGIN    -- SELECT DISTNICT for query results    FOR _key_id IN    SELECT DISTINCT at_name      FROM report.company_data_date cd       JOIN report.company_data_amount cda ON cd.id = cda.company_data_date_id       JOIN report.amount_types at ON cda.amount_type_id  = at.id      WHERE date_start BETWEEN i_start_date AND i_end_date       AND group_id = ANY (i_group_id)       AND interval_type_id = i_interval    LOOP    -- build function_call with datatype of column        IF char_length(_text_op) > 1 THEN            _text_op := _text_op || ', ' || _key_id || ' NUMERIC(20,2)';        ELSE            _text_op := _text_op || _key_id || ' NUMERIC(20,2)';        END IF;    END LOOP;    -- build query with parameter filters    _ret = '        SELECT * FROM crosstab(''SELECT date_start, at.at_name,  cda.amount ct           FROM report.company_data_date cd           JOIN report.company_data_amount cda ON cd.id = cda.company_data_date_id           JOIN report.amount_types at ON cda.amount_type_id  = at.id          WHERE date_start between $$' || i_start_date::TEXT || '$$ AND $$' || i_end_date::TEXT || '$$            AND interval_type_id = ' || i_interval::TEXT || '            AND group_id = ANY (ARRAY[' || array_to_string(i_group_id, ',') || '])         ORDER BY date_start'')             AS ct (date_start timestamptz, ' || _text_op || ')';    RETURN QUERY    SELECT _ret;END;$ab$ LANGUAGE 'plpgsql';

Call the function to get the string, then execute the string. I think I tried executing it in the function, but it didn't work well.

I ran into the same problem when I needed to update some JSON and remove a few elements in my database. This query below worked well enough for me, as it preserves the string quotes but does not add them to numbers.

select  '{' || substr(x.arr, 3, length(x.arr) - 4) || '}'from(  select     replace(replace(cast(array_agg(xx) as varchar), '\"', '"'), '","', ', ') as arr  from  (  select    elem.key,    elem.value,    '"' || elem.key || '":' || elem.value as xx  from    quote q      cross join    json_each(q.detail::json -> 'bQuoteDetail'-> 'quoteHC'->0)  as elem    where    elem.key != 'allRiskItems'    ) f ) x