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

[Regina Obe]

Oh forgot one more very useful operator, the subtraction operator.  Removes a key/value from the list:

 

SELECT '{"color": "blue", "height_m": 10}'::jsonb - 'color'

 

Returns:

{"height_m": 10}

 

 

From: Regina Obe [mailto:lr at pcorp.us] 
Sent: Friday, January 7, 2022 11:18 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?

 

David,

 

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}';

 

Returns:

{"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)

SELECT kv.*

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

 

Returns:

color      red

width_m              5

height_m             10

 

Now lets do this with PostGIS :)

WITH a AS (

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 <mailto: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?

 

Regards,

 

David

 

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);

TRUNCATE tABLE test;

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,

TYPE FID_MEASURE IS TABLE OF NUMBER INDEX BY VARCHAR2(38);

NODES_WAITING FID_SET;

 

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

 

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

 

Regards,

 

David

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

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