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

[Shaozhong SHI]

Hi, Regina,

This looks offering some clarity and simplicity.

I was told that hstore can also work as associative array.  Does it offer
clarity and simplicity?

Regards,

Shao

On Sat, 8 Jan 2022 at 04:20, Regina Obe <lr at pcorp.us> wrote:

> 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 J
>
> 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
> <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?
>
>
>
> Regards,
>
>
>
> David
>
>
>
> On Fri, 7 Jan 2022 at 21:19, Regina Obe <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] *On
> Behalf Of *Shaozhong SHI
> *Sent:* Wednesday, January 5, 2022 1:30 PM
> *To:* PostGIS Users Discussion <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
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> 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/20220111/299f405f/attachment.html>