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

Question

I have a data model like the following which is simplified to show you only this problem (SQL Fiddle Link at the bottom):

A person is represented in the database as a meta table row with a name and with multiple attributes which are stored in the data table as key-value pair (key and value are in separate columns).

Expected Result

Now I would like to retrieve all users with all their attributes. The attributes should be returned as json object in a separate column. For example:

name, data
Florian, { "age":23, "color":"blue" }
Markus, { "age":24, "color":"green" }

My Approach

Now my problem is, that I couldn't find a way to create a key-value pair in postgres. I tried following:

SELECT
  name,
  array_to_json(array_agg(row(d.key, d.value))) AS data
FROM meta AS m
JOIN (
  SELECT d.fk_id, d.key, d.value AS value FROM data AS d
  ) AS d
ON d.fk_id = m.id
GROUP BY m.name;

But it returns this as data column:

[{"f1":"age","f2":24},{"f1":"color","f2":"blue"}]

Other Solutions

I know there is the function crosstab which enables me to turn the data table into a key as column and value as row table. But this is not dynamic. And I don't know how many attributes a person has in the data table. So this is not an option.

I could also create a json like string with the two row values and aggregate them. But maybe there is a nicer solution.

And no, it is not possible to change the data-model because the real data model is already in use of multiple parties.

SQLFiddle

Check and test out the fiddle i've created for this question: http://sqlfiddle.com/#!15/bd579/14

klin · Accepted Answer · 2021-11-03 13:56:05Z

15

Use the aggregate function json_object_agg(key, value):

select 
    name, 
    json_object_agg(key, value) as data
from data
join meta on fk_id = id
group by 1;

Db<>Fiddle.

The function was introduced in Postgres 9.4.

edited Nov 3, 2021 at 13:56

answered Aug 14, 2015 at 15:00

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Caullyn · Accepted Answer · 2015-08-14 14:55:49Z

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.

Bitter · Accepted Answer · 2017-09-19 14:15:05Z

0

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

answered Sep 19, 2017 at 14:15

Bitter

211 silver badge4 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related