I am doing an example of indexing with JSONB in PostgreSQL and want add random uuid to a piece of JSON like below. However I can't get the syntax just right the closest I have got is "{"lookup_id": " || uuid || "}".
But I require
{"lookup_id": "92b3b21a-a87c-1798-5d91-3dbf3043c209"}
My code is:
INSERT INTO test (id, json)
SELECT x.id, '{
"lookup_id": " || uuid || "
}'::jsonb
FROM generate_series(1,100) AS x(id),
uuid_in(md5(now()::text)::cstring) AS uuid;
you can use row_to_json function:
select x.id, row_to_json(r.*)::jsonb
from generate_series(1,100) AS x(id)
cross join (select uuid_in(md5(now()::text)::cstring) as lookup_id) as r;
update
first, you can use uuid so you can create unique uids:
CREATE EXTENSION "uuid-ossp";
with cte as (
select
*, uuid_generate_v4() as uuid
from generate_series(1,5) AS x(id)
)
select distinct uuid from cte
------------------------------------------------
"e980c784-8aae-493f-90fb-1091280fe4f7"
"45a80660-3be8-4538-a039-13d97d6306af"
"5380f285-5d6b-467a-a83a-7fdc5c0ebc4c"
"7a435b36-95d3-49fc-808f-359838a866ed"
"3164a544-a2c9-4cd0-b0c4-199a99986cea"
next, merging this to your existing json. The stupid and easiest way for now could be something like this:
with cte as (
select
'{"a":1}'::json as j, uuid_generate_v4() as uuid
from generate_series(1,5) AS x(id)
)
select
left(j::text, length(j::text) - 1) || ', "uuid":' || to_json(uuid) || '}'
from cte
But you can also write some function to merge jsons together, or you can use hstore extension to merge jsons together:
with cte as (
select
id, '{"a":1, "b":2}'::json as data, uuid_generate_v4() as uuid
from generate_series(1,5) AS x(id)
), cte2 as (
select
id,
(
select hstore(array_agg(r.key), array_agg(r.value))
from (
select *
from json_each_text(c.data) as j
union all
select 'uuid', c.uuid::text
) as r
) as data
from cte as c
)
select
id, hstore_to_json(data)
from cte2
And I'm sure bigger experts on PostgreSQL could advice more elegant way to merge jsons together
