[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] How best to create and use associative array type in Postgres?
From: Shaozhong SHI <shishaozhong () gmail ! com>
Date: 2022-01-11 16:04:10
Message-ID: CA+i5JwaUUeEL5vPj+-cC-8tuZVPzGt-wpDNnMt-ew3ozRnZC4w () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@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@pcorp.us]
> *Sent:* Friday, January 7, 2022 11:18 PM
> *To:* 'PostGIS Users Discussion' <postgis-users@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@lists.osgeo.org
> <postgis-users-bounces@lists.osgeo.org>] *On Behalf Of *Shaozhong SHI
> *Sent:* Friday, January 7, 2022 9:25 PM
> *To:* PostGIS Users Discussion <postgis-users@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@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@lists.osgeo.org] *On
> Behalf Of *Shaozhong SHI
> *Sent:* Wednesday, January 5, 2022 1:30 PM
> *To:* PostGIS Users Discussion <postgis-users@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@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
[Attachment #5 (text/html)]
<div dir="ltr">Hi, Regina,<div><br></div><div>This looks offering some clarity and \
simplicity.</div><div><br></div><div>I was told that hstore can also work as \
associative array. Does it offer clarity and \
simplicity?</div><div><br></div><div>Regards,</div><div><br></div><div>Shao</div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sat, 8 Jan 2022 at 04:20, \
Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div lang="EN-US"><div \
class="gmail-m_-7089707963458799930WordSection1"><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Oh forgot \
one more very useful operator, the subtraction operator. Removes a key/value from \
the list:<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">SELECT \
'{"color": "blue", "height_m": 10}'::jsonb - \
'color'<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Returns:<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">{"height_m": \
10}<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><div \
style="border-top:none;border-right:none;border-bottom:none;border-left:1.5pt solid \
blue;padding:0in 0in 0in 4pt"><div><div \
style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid \
rgb(225,225,225);padding:3pt 0in 0in"><p class="MsoNormal"><b><span \
style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span \
style="font-size:11pt;font-family:Calibri,sans-serif"> Regina Obe [mailto:<a \
href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>] <br><b>Sent:</b> Friday, \
January 7, 2022 11:18 PM<br><b>To:</b> 'PostGIS Users Discussion' <<a \
href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> RE: \
[postgis-users] How best to create and use associative array type in \
Postgres?<u></u><u></u></span></p></div></div><p class="MsoNormal"><u></u> \
<u></u></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">David,<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Not sure \
what you are asking? There are many functions for jsonb and even more the newer your \
PostgreSQL is.<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Take a \
look at - <a href="https://www.postgresql.org/docs/current/functions-json.html" \
target="_blank">https://www.postgresql.org/docs/current/functions-json.html</a><u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">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.<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">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<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Take for \
example:<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">SELECT \
'{"color": "blue", "height_m": 10}'::jsonb || \
'{"color": "red"}'::jsonb || '{"width_m": \
5}';<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Returns:<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">{"color": \
"red", "width_m": 5, "height_m": \
10}<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">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.<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">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.<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Here is an \
example:<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">WITH a AS \
(SELECT '{"color": "blue", "height_m": \
10}'::jsonb || '{"color": "red"}'::jsonb || \
'{"width_m": 5}' AS data)<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">SELECT \
kv.*<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">FROM a, \
jsonb_each_text(a.data) AS kv;<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Returns:<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">color \
red<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">width_m \
5<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">height_m \
10<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Now lets \
do this with PostGIS </span><span \
style="font-size:11pt;font-family:Wingdings;color:rgb(31,73,125)">J</span><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">WITH a AS \
(<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">SELECT \
ST_AsGeoJSON(ST_MakeLine( ARRAY[ST_Point(1,2), ST_Point(3,4), \
ST_Point(-9,1)]))::jsonb AS data<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> \
)<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">SELECT \
kv.key, kv.value, kv.value->2->>0 AS last_x<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">FROM a, \
jsonb_each(a.data) AS kv;<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> \
key | value | \
last_x<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">-------------+---------------------------+--------<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">type \
| "LineString" |<u></u><u></u></span></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">coordinates \
| [[1, 2], [3, 4], [-9, 1]] | -9<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">(2 \
rows)<u></u><u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> \
<u></u></span></p><div \
style="border-top:none;border-right:none;border-bottom:none;border-left:1.5pt solid \
blue;padding:0in 0in 0in 4pt"><div><div \
style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid \
rgb(225,225,225);padding:3pt 0in 0in"><p class="MsoNormal"><b><span \
style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span \
style="font-size:11pt;font-family:Calibri,sans-serif"> postgis-users [<a \
href="mailto:postgis-users-bounces@lists.osgeo.org" \
target="_blank">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of \
</b>Shaozhong SHI<br><b>Sent:</b> Friday, January 7, 2022 9:25 PM<br><b>To:</b> \
PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: \
[postgis-users] How best to create and use associative array type in \
Postgres?<u></u><u></u></span></p></div></div><p class="MsoNormal"><u></u> \
<u></u></p><div><p class="MsoNormal">Hi, Regina,<u></u><u></u></p><div><p \
class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">That is \
interesting!<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> \
<u></u></p></div><div><p class="MsoNormal">How to add new entries to the dictionary. \
E.g., weight: 40?<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> \
<u></u></p></div><div><p class="MsoNormal">Can the dictionary to serve as a \
collection of paired key, value set, so that we can accumulate data to be \
processed?<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> \
<u></u></p></div><div><p class="MsoNormal">Then, we can deal with the first, then 2nd \
and so on in turn?<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> \
<u></u></p></div><div><p class="MsoNormal">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?<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> \
<u></u></p></div><div><p class="MsoNormal">Alternatively, can we fetch each key, \
value pair by its index or position?<u></u><u></u></p></div><div><p \
class="MsoNormal"><u></u> <u></u></p></div><div><p \
class="MsoNormal">Regards,<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> \
<u></u></p></div><div><p class="MsoNormal">David<u></u><u></u></p></div></div><p \
class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal">On Fri, 7 Jan \
2022 at 21:19, Regina Obe <<a href="mailto:lr@pcorp.us" \
target="_blank">lr@pcorp.us</a>> wrote:<u></u><u></u></p></div><blockquote \
style="border-top:none;border-right:none;border-bottom:none;border-left:1pt solid \
rgb(204,204,204);padding:0in 0in 0in 6pt;margin:5pt 0in 5pt 4.8pt"><div><div><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Use JSONB \
datatype. </span><u></u><u></u></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> \
</span><u></u><u></u></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">CREATE \
TABLE test(id integer, data jsonb);</span><u></u><u></u></p><p \
class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">TRUNCATE \
tABLE test;</span><u></u><u></u></p><p class="MsoNormal"><span \
style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">INSERT \
INTO test(id, data)</span><u></u><u></u></p><p class="MsoNormal"><span \
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> <a \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br> \
</blockquote></div>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic