Postgresql - Best Way to Return an Array of Key-Value Pairs

PostgreSQL - best way to return an array of key-value pairs

I suspect that without having more knowledge of your application I'm not going to be able to get you all the way to the result you need. But we can get pretty far. For starters, there is the ROW function:

# SELECT 'foo', ROW(3, 'Bob');
?column? | row
----------+---------
foo | (3,Bob)
(1 row)

So that right there lets you bundle a whole row into a cell. You could also make things more explicit by making a type for it:

# CREATE TYPE person(id INTEGER, name VARCHAR);
CREATE TYPE
# SELECT now(), row(3, 'Bob')::person;
now | row
-------------------------------+---------
2012-02-03 10:46:13.279512-07 | (3,Bob)
(1 row)

Incidentally, whenever you make a table, PostgreSQL makes a type of the same name, so if you already have a table like this you also have a type. For example:

# DROP TYPE person;
DROP TYPE

# CREATE TABLE people (id SERIAL, name VARCHAR);
NOTICE: CREATE TABLE will create implicit sequence "people_id_seq" for serial column "people.id"
CREATE TABLE

# SELECT 'foo', row(3, 'Bob')::people;
?column? | row
----------+---------
foo | (3,Bob)
(1 row)

See in the third query there I used people just like a type.

Now this is not likely to be as much help as you'd think for two reasons:

  1. I can't find any convenient syntax for pulling data out of the nested row.

    I may be missing something, but I just don't see many people using this syntax. The only example I see in the documentation is a function taking a row value as an argument and doing something with it. I don't see an example of pulling the row out of the cell and querying against parts of it. It seems like you can package the data up this way, but it's hard to deconstruct after that. You'll wind up having to make a lot of stored procedures.

  2. Your language's PostgreSQL driver may not be able to handle row-valued data nested in a row.

    I can't speak for NPGSQL, but since this is a very PostgreSQL-specific feature you're not going to find support for it in libraries that support other databases. For example, Hibernate isn't going to be able to handle fetching an object stored as a cell value in a row. I'm not even sure the JDBC would be able to give Hibernate the information usefully, so the problem could go quite deep.

So, what you're doing here is feasible provided you can live without a lot of the niceties. I would recommend against pursuing it though, because it's going to be an uphill battle the whole way, unless I'm really misinformed.

PostgreSQL return JSON objects as key-value pairs

This will flatten the json structure and effectively just concatenate the values, along with the top-level key names (e.g. temperature and pressure), for the expected "depth" level. See if this is what you had in mind.

SELECT
id,
(
SELECT STRING_AGG(conc, ',')
FROM (
SELECT CONCAT_WS(',', key, STRING_AGG(value, ',')) AS conc
FROM (
SELECT key, (jsonb_each_text(value)).value
FROM jsonb_each(data->'observations')
) AS x
GROUP BY key
) AS csv
) AS csv
FROM mytable

Result:

| id  | csv                                                 |
| --- | --------------------------------------------------- |
| 1 | pressure,float,mbar,1011.3,temperature,float,C,23.1 |
| 2 | pressure,bigint,unk,455,temperature,int,F,45 |

https://www.db-fiddle.com/f/ada5mtMgYn5acshi3WLR7S/0

Using a key value pair list/array in postgres json

A JSON column is (in all likelihood) a very bad idea. The right way in SQL is to use a junction table:

create table BookUsers (
BookUserId serial primary key,
BookId int references Books(BookId),
UserId int references Users(UserId),
TargetDate date,
. . .
);

a row for every json key/value pair in postgresql

You want jsonb_each_text() (unnest is for arrays)

select t."timestamp", 
p.*
from the_table t
cross join jsonb_each_text(t.payload) as p(id, data)

Get multiple key/value pairs from jsonb (pg 12)

EDIT

by the following method we can get the desired object w/o getting a row for each label (this can be prohibitive when dealing w/ millions of rows)

  1. Get array of desired labels
  2. Remove these keys from the attributes object
  3. Get the remaining keys
  4. Remove those from the original attributes object, leaving behind the key/values corresponding to the desired labels e.g.,
with table_1 (layer_id, attributes) as (
values (5, '{"a": 1, "b": 3, "c": "hello"}'::jsonb),
(7, '{"d": 0, "e": 9, "f": "bye"}'::jsonb)
), table_2 (layer_id, labels) as (
values (5, '["a", "c"]'::jsonb),
(7, '[]'::jsonb)
)
select t1.layer_id,
a.attributes result
from table_1 t1
join table_2 t2 on t2.layer_id = t1.layer_id
cross join lateral (
SELECT t1.attributes - ARRAY (
SELECT
jsonb_object_keys(attributes - ARRAY (
SELECT
jsonb_array_elements_text(labels)))) AS attributes) a
group by t1.layer_id, a.attributes

layer_id | result
----------+------------------------
5 | {"a": 1, "c": "hello"}
7 | []
(2 rows)

ORIGINAL SOLUTION

Expand using jsonb_array_elements(), and then aggregate with jsonb_object_agg():

with table_1 as (
select 5 as layer_id, '{"a": 1, "b": 3, "c": "hello"}'::jsonb as attributes
), table_2 as (
select 5 as layer_id, '["a", "c"]'::jsonb as labels
)
select t2.layer_id, jsonb_object_agg(l.label, t1.attributes->l.label)
from table_2 t2
cross join lateral jsonb_array_elements_text(t2.labels) as l(label)
join table_1 t1 on t1.layer_id = t2.layer_id
group by t2.layer_id;

layer_id | jsonb_object_agg
----------+------------------------
5 | {"a": 1, "c": "hello"}
(1 row)

To deal with empty labels elements, try the below. I was not able to figure out how to get this to work without making the original query into a CTE since jsonb_object_agg() will not accept null for a label.

with table_1 (layer_id, attributes) as (
values (5, '{"a": 1, "b": 3, "c": "hello"}'::jsonb),
(7, '{"d": 0, "e": 9, "f": "bye"}'::jsonb)
), table_2 (layer_id, labels) as (
values (5, '["a", "c"]'::jsonb),
(7, '[]'::jsonb)
), expand as (
select t1.layer_id,
jsonb_object_agg(l.label, t1.attributes->l.label) result
from table_1 t1
join table_2 t2 on t2.layer_id = t1.layer_id
cross join lateral jsonb_array_elements_text(t2.labels) as l(label)
group by t1.layer_id
)
select t1.layer_id, coalesce(x.result, '[]'::jsonb) as result
from table_1 t1
left join expand x
on x.layer_id = t1.layer_id
;

layer_id | result
----------+------------------------
5 | {"a": 1, "c": "hello"}
7 | []
(2 rows)

Find object by key/value in an array in postgresql jsonb column

If you are using Postgres 12, you can use a JSON path expression:

select jsonb_path_query_array(content, '$[*] ? (@.petId == 1234)') as content
from api_data
where content @> '[{"petId":1234}]';

If you are using an older version, you need to unnest and aggregate manually:

select (select jsonb_agg(e)
from jsonb_array_elements(d.content) as t(e)
where t.e @> '{"petId":1234}') as content
from api_data d
where d.content @> '[{"petId":1234}]'

Add a new key/value pair into a nested array inside a PostgreSQL JSON column

You have to use the jsonb_set function while specifying the right path see the manual.

For a single json update :

UPDATE sample_table_json
SET json = jsonb_set( json::jsonb
, '{post,0,active}'
, 'true'
, true
)

For a (very) limited set of json updates :

UPDATE sample_table_json
SET json = jsonb_set(jsonb_set( json::jsonb
, '{post,0,active}'
, 'true'
, true
)
, '{post,1,active}'
, 'true'
, true
)

For a larger set of json updates of the same json data, you can create the "aggregate version" of the jsonb_set function :

CREATE OR REPLACE FUNCTION jsonb_set(x jsonb, y jsonb, p text[], e jsonb, b boolean)
RETURNS jsonb LANGUAGE sql AS $$
SELECT jsonb_set(COALESCE(x,y), p, e, b) ; $$ ;

CREATE OR REPLACE AGGREGATE jsonb_set_agg(x jsonb, p text[], e jsonb, b boolean)
( STYPE = jsonb, SFUNC = jsonb_set) ;

and then use the new aggregate function jsonb_set_agg while iterating on a query result where the path and val fields could be calculated :

SELECT jsonb_set_agg('{"username": "jsmith","location": "United States","posts": [{"id":"1","title":"Welcome"},{"id":"4","title":"What started it all"}]}' :: jsonb
, l.path :: text[]
, to_jsonb(l.val)
, true)
FROM (VALUES ('{posts,0,active}', 'true'), ('{posts,1,active}', 'true')) AS l(path, val) -- this list could be the result of a subquery

This query could finally be used in order to update some data :

WITH list AS
(
SELECT id
, jsonb_set_agg(json :: jsonb
, l.path :: text[]
, to_jsonb(l.val)
, true) AS res
FROM sample_table_json
CROSS JOIN (VALUES ('{posts,0,active}', 'true'), ('{posts,1,active}', 'true')) AS l(path, val)
GROUP BY id
)
UPDATE sample_table_json AS t
SET json = l.res
FROM list AS l
WHERE t.id = l.id

see the test result in dbfiddle

Remove long key value pairs in jsonb column in postgres with SQL

There is no built-in function for this. You will need to write your own.

Something along the lines:

create function remove_long_values(p_input jsonb, p_maxlen int)
returns jsonb
as
$$
select coalesce(jsonb_object_agg(e.ky, e.val), '{}')
from jsonb_each(p_input) as e(ky,val)
where length(e.val::text) <= p_maxlen;
$$
language sql
immutable
parallel safe;

The above does not deal with nested key/value pairs! It only checks this on the first level.

Then use it in the query:

CREATE MATERIALIZED VIEW IF NOT EXISTS test_materialized_view 
AS
SELECT t1.id, t1.data1 || t1.data2 || remove_long_values(t2.data1,250) as "data"
FROM table_1 t1
LEFT JOIN table_2 t2 ON (...);


Related Topics



Leave a reply



Submit