[postgis-users] How best to create and use associative array type in Postgres?

Regina Obe lr at pcorp.us
Fri Jan 7 20:17:37 PST 2022



Not sure what you are asking? There are many functions for jsonb and even more the newer your PostgreSQL is.

Take a look at - https://www.postgresql.org/docs/current/functions-json.html



You can pull arrays by index but not really key/values by index (because jsonb reorders keys/values for efficiency).   So order shouldn’t matter in jsonb as the same level keys are unique.

The trick of using the concatenation operator (||) to update keys values works, because the last entry for a key wins, and any key not in the list gets replaced by the last one.  So I guess your popping idea


Take for example:


SELECT '{"color": "blue", "height_m": 10}'::jsonb || '{"color": "red"}'::jsonb || '{"width_m": 5}';



{"color": "red", "width_m": 5, "height_m": 10}


Note how the entry width_m was added, but not the order you specified it, and that the color was changed from blue to red.


Now if you wanted to get a set of all the key value pairs, you’d use jsonb_each_text (to get value as text) or jsonb_each to get the value as a jsonb.


Here is an example:

WITH a AS (SELECT '{"color": "blue", "height_m": 10}'::jsonb || '{"color": "red"}'::jsonb || '{"width_m": 5}' AS data)


FROM a, jsonb_each_text(a.data) AS kv;



color      red

width_m              5

height_m             10


Now lets do this with PostGIS :)


SELECT ST_AsGeoJSON(ST_MakeLine( ARRAY[ST_Point(1,2), ST_Point(3,4), ST_Point(-9,1)]))::jsonb AS data


SELECT kv.key, kv.value, kv.value->2->>0 AS last_x

FROM a, jsonb_each(a.data) AS kv;


     key     |           value           | last_x


type        | "LineString"              |

coordinates | [[1, 2], [3, 4], [-9, 1]] | -9

(2 rows)




From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Shaozhong SHI
Sent: Friday, January 7, 2022 9:25 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] How best to create and use associative array type in Postgres?


Hi, Regina,


That is interesting!


How to add new entries to the dictionary.  E.g., weight: 40?


Can the dictionary to serve as a collection of paired key, value set, so that we can accumulate data to be processed?


Then, we can deal with the first, then 2nd and so on in turn?


Or, we can do things like pip and pop?  Namely, when we have dealt with the first key, value pair, it will be out the dictionary, so that we can be sure that we are dealing with each key, value pair in turn?


Alternatively, can we fetch each key, value pair by its index or position?






On Fri, 7 Jan 2022 at 21:19, Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:

Use JSONB datatype.  


CREATE TABLE test(id integer, data jsonb);


INSERT INTO test(id, data)

VALUES (1, '{"color": "red", "height_m": 10}');


-- PG14 or higher – you can used subscript feature

UPDATE test SET data['color'] = to_jsonb('blue'::text), 

    data['height_m'] = to_jsonb(10), data['width_m'] = to_jsonb(2)

WHERE id = 1;


-- PG14 or lower

UPDATE test SET data = jsonb_set(data, ARRAY['color'], to_jsonb('blue'::text), true)

WHERE id = 1;


-- PG14 or lower to set multiple

UPDATE test SET data = data || '{"color": "blue", "height_m": 10}'::jsonb;


-- To read (all versions)

SELECT data->>'color' AS color, (data->>'height_m')::integer As height_m

FROM test;

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org> ] On Behalf Of Shaozhong SHI
Sent: Wednesday, January 5, 2022 1:30 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Subject: [postgis-users] How best to create and use associative array type in Postgres?



In Oracle, one can create and use associative array.  For instance,




How best to create and use associative array type in Postgres?


Or, what is the best/most efficient equivalent in Postgres?





postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220107/6be23041/attachment.html>

More information about the postgis-users mailing list