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 <[email protected]> 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:[email protected]] > *Sent:* Friday, January 7, 2022 11:18 PM > *To:* 'PostGIS Users Discussion' <[email protected]> > *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:[email protected] > <[email protected]>] *On Behalf Of *Shaozhong SHI > *Sent:* Friday, January 7, 2022 9:25 PM > *To:* PostGIS Users Discussion <[email protected]> > *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 <[email protected]> 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:[email protected]] *On > Behalf Of *Shaozhong SHI > *Sent:* Wednesday, January 5, 2022 1:30 PM > *To:* PostGIS Users Discussion <[email protected]> > *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 > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
