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
In order to set a key-value pair in a KiiObject, call the set() method of the KiiObject class. The set() method has an overloaded version for each data type. Specify a key-value pair as arguments of the set() method. The specified key-value pair will be saved at the first level of the JSON document hierarchy.
To convert this PostgreSQL array into JSON, we can use the array_to_json function.
If you're using static JSON data and active data that's structured for SQL storage, Postgres is a good shout — its JSONB representation is efficient and allows for indexing.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With