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