Advertisements I have a table with columns in jsonb format which contains data like this: [‘A’, ‘B’, ‘C’] I want to convert column to array column type but I don’t know how to do it properly. Example of my script structure: ALTER TABLE "products" ADD COLUMN "test_array" VARCHAR(2)[]; UPDATE "products" SET test_array = do converting… Read More Convert data from JSONB type to Array type in PostgreSQL
Advertisements data jsonb NOT NULL DEFAULT $${}$$::jsonb what does $${}$$ on table creation? Can’t find anything on the internet. Thanks >Solution : In Postgres $$ (or $anything$) is an alternative for a single quotation mark (‘) to enclose character literals. In other words, $${}$$ is equivalent to ‘{}’. It’s commonly used to quote strings that… Read More what does $${}$$::json in Postgres mean?
Advertisements I have a table in postgres which has rows like the following in a jsonb column called data: { "block": { "data": null, "timestamp": "1680617159" } } {"block": {"hash": "0xf0cab6f80ff8db4233bd721df2d2a7f7b8be82a4a1d1df3fa9bbddfe2b609e28", "size": "0x21b", "miner": "0x0d70592f27ec3d8996b4317150b3ed8c0cd57e38", "nonce": "0x1a8261f25fc22fc3", "number": "0x1847", "uncles": [], "gasUsed": "0x0", "mixHash": "0x864231753d23fb737d685a94f0d1a7ccae00a005df88c0f1801f03ca84b317eb", "gasLimit": "0x1388", "extraData": "0x476574682f76312e302e302f6c696e75782f676f312e342e32", "logsBloom": "0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000", "stateRoot": "0x2754a138df13677ca025d024c6b6ac901237e2bf419dda68d9f9519a69bfe00e", "timestamp": "0x55baa522",… Read More How to query postgres for jsonb field that has a key set to a null value
Advertisements I have two tables: _building: id cityId location 34891 111 {"regionId": "55", "regionName": null, "full address": "…"} 09372 222 {"regionId": null, "regionName": null, "full address": "…"} _city: id name location 111 A {"regionId": "55", "regionName": "Magic region 11"} 222 B {"regionId": "238", "regionName": "Magic region 23"} I need to enrich the location column of… Read More Updating with jsonb_set
Advertisements I have the following content in a jsonb column: [ { "Value": "ABC", "PropertyTypeId": 1 }, { "Value": "CDE", "PropertyTypeId": 2 }, { "Value": "FGE", "PropertyTypeId": 3 } ] And I want to get the value of the element that has a property type of 2 for example. I’ve gotten as closes as the… Read More select a single json element based on a property value
Advertisements Somehow populating a database with a JSONB column ended up with every value in the column being a JSONB string instead of an object. => select specifications from checklist_item; specifications ——————— "{}" "{}" "{\"x\": 2, \"y\": \"z\"}" Is it possible to update, in a single statement, each of these values to JSONB objects as… Read More Postgres JSONB values are all strings
Advertisements I have a postrgresql table with jsonb products column: | products | | ————————————————— | | [{"id": "eaaca8bc-c8a0-45f7-9698-d4fc701d2e5a", "@type": "@game", "extId": "da32af17-fa03-4a62-bd04-f026d04d16e9"}, {"id": "5fc5de21-9cb7-4bd3-a723-7936bfef7cde", "@type": "@book", "extId": "c945f005-2d37-491c-8ba9-9da2709a3aab"}, {"id": "892fe85c-d7d6-4815-8dec-1720b644205a", "@type": "@sport", "extId": "c252dcba-2a14-4e75-90db-29ccac2499d2"}] | | [{"id": "gh6d86ls-wj8o-39r4-2694-1720b644205a", "@type": "@game", "extId": "da32af17-fa03-4a62-bd04-f026d04d16e9"}] | | | | [{"id": "892fe85c-d7d6-4815-8dec-1720b644205a", "@type": "@sport", "extId": "c252dcba-2a14-4e75-90db-29ccac2499d2"}] | Example… Read More Query for jsonb. How select by value from jsonb?
Advertisements I have a table with a column of jsonb type, the JSON object is of key:value pairs, the problem is, I need to update one of the keys to contain a UUID instead of the original value. The update statement I’m using: UPDATE public.applications a SET data = jsonb_set(data, ‘{id}’, (SELECT b.uuid FROM public.devices… Read More Update JSONB with UUID value
Advertisements I have a simple table with a Jsonb array of phone numbers: CREATE TEMP TABLE IF NOT EXISTS user_info ( id serial, phone_numbers jsonb ); INSERT INTO user_info values (1, ‘["123456"]’),(2, ‘["564789"]’), (3, ‘["564747", "545884"]’); SQLFiddle: SQLFiddle Link now, I want to group the array of numbers into columns. something like: phone_numbers id 123456… Read More Convert Jsonb simple array into column group by
Advertisements I have a Postgres json field products with jsonb like this { "user": "7871425f-abb4-42c6-8e4d-36c99ec2f67c", "product": { "ef688ed3-12c9-4e22-af5f-32a7b32db628": { "category": "basic" }, "8fbef749-42dd-4a40-a98a-ebe7b54c4388": { "category": "advanced" }, "d475185f-d014-4c60-aebd-cecaed7df550": { } } } I am trying to extract category for each product to get something like "ef688ed3-12c9-4e22-af5f-32a7b32db628": "basic" "8fbef749-42dd-4a40-a98a-ebe7b54c4388": "advanced" "d475185f-d014-4c60-aebd-cecaed7df550": NULL Any help highly appreciated… Read More Postgres JSONB Query searching dynamic path