Hello,
I'm fairly new to PostgreSQL and have a situation where I need to flatten a text column to two separate columns.
Below is the query I'm using:
[CODE]SELECT s.CoolerShelf,
s.ShelfPosition ,
FROM planogram
CROSS JOIN LATERAL UNNEST(string_t o_array(shelves , ','))
WITH ORDINALITY s(CoolerShelf,S helfPosition)
code
code;
Below is the result set from the above query:
coolershelf shelfposition
[["8d2cf35d-5708-45e0-9cb6-acad358e0f92" 1
"5a91f7a2-029a-46d7-8440-9337dd1b87d3" 2
"521562a9-9d33-438d-8156-1e6b1874ec8e" 3
"e14817e4-6630-4dca-a188-ac71060dcac9" 4
"76967052-ba9d-43f5-afd4-b4bbe1452d7e" 5
"2e5a6fb2-071e-426b-ac55-69f16baa0b42" 6
"108f263d-ee78-4124-a94b-2c5641f90321" 7
"0dbe5016-9e78-4173-b6e6-ff3e0199ca2e"] 8
["9bd83b79-186d-4ae5-9373-956dbd515070" 9
"b6172191-fa44-436d-879d-c883e4d240ed" 10
"093b72ba-74cd-48b9-86df-7e7d9341ae53" 11
"88b6c7f8-1d23-4e82-b959-8cb3400cc039" 12
"8279d979-8a57-4595-b9d3-346f6b05924e" 13
"735e6139-0fce-4bb7-a4a2-00ceb86c9b07" 14
"0ad84c4f-e0d8-4606-b563-8b2e32cc632f" 15
"5a86f7ea-0763-4473-ba09-91398e938be7"] 16
["62b2b9c6-1991-48f7-8533-76fa877e9736" 17
"35b56ed8-74f0-42f8-ab1c-ede41605b7bd" 18
"71848241-6348-4fde-935e-74a5c369ede1" 19
"722f05a6-5672-4be6-902d-635372e04758" 20
"b2a45221-aafb-4949-8018-5fed6cf7c7fe" 21
"dbb49783-5f75-4b3c-a793-a933ea321679" 22
"6bb25395-6647-4668-9e6e-158ad5f0b8af" 23
"1b32e613-8e72-420f-b31f-7bc95650386c"] 24
["636e2084-fdeb-4594-a400-10f6ef2791d7" 25
"8ac273ab-b8b2-46af-a8b4-f8fb22afe8e4" 26
"372e4f00-4ce9-4a9f-927b-d34c5a4968c1" 27
"f821abb1-d97e-4d99-b630-f74de5d106c1" 28
"d40b9b64-e81d-4133-bde2-54975806c087" 29
"07937692-680f-4cb0-8d17-98684141b92c" 30
"3b2039d0-de86-4cd7-9fb2-21397932f14c" 31
"16c24542-65c8-45db-97dc-014e66db7ef0"] 32
["f67efbcc-898d-4b50-8c15-21ac4fbbc500" 33
"64c020c7-9bd1-4e00-968f-180e3d68e100" 34
"3667915a-8e10-41fb-8f00-035cc10324a6" 35
"b7bc23c3-f5a1-486c-a99a-6c61357ed000" 36
"11292acd-ef71-4e0c-8281-0f50007cf850" 37
"210cca62-61b4-4ed9-ad42-653a909d3045" 38
"1dd2468a-0a3c-43e6-aae6-bd702d1c8a74" 39
"d7f8e5ee-1e05-42e1-8ff4-89529a210a76"] 40
["a1de7674-fa0b-49e6-af6a-2522798c4861" 41
"5cca7cd7-f50b-4538-ad89-a85b2d72a555" 42
"30cd353c-ee8c-4a94-9fbd-166372c2fd96" 43
"7407ab86-fdf6-4bf1-8282-e1218e021ed3" 44
"20ce7593-b1e2-4401-9b7c-1af18ec37f6c" 45
"541e995a-1416-4f4b-9696-2827cdcbd64e" 46
"3247610e-0486-4891-8fce-32f2e03fcaec" 47
"6492db47-54af-4390-9c88-11f43c3eaef0"]] 48
The desired results should look like as follows:
coolershelf shelfposition
[["8d2cf35d-5708-45e0-9cb6-acad358e0f92" 0
"5a91f7a2-029a-46d7-8440-9337dd1b87d3" 1
"521562a9-9d33-438d-8156-1e6b1874ec8e" 2
"e14817e4-6630-4dca-a188-ac71060dcac9" 3
"76967052-ba9d-43f5-afd4-b4bbe1452d7e" 4
"2e5a6fb2-071e-426b-ac55-69f16baa0b42" 5
"108f263d-ee78-4124-a94b-2c5641f90321" 6
"0dbe5016-9e78-4173-b6e6-ff3e0199ca2e"] 7
["9bd83b79-186d-4ae5-9373-956dbd515070" 0
"b6172191-fa44-436d-879d-c883e4d240ed" 1
"093b72ba-74cd-48b9-86df-7e7d9341ae53" 2
"88b6c7f8-1d23-4e82-b959-8cb3400cc039" 3
"8279d979-8a57-4595-b9d3-346f6b05924e" 4
"735e6139-0fce-4bb7-a4a2-00ceb86c9b07" 5
"0ad84c4f-e0d8-4606-b563-8b2e32cc632f" 6
"5a86f7ea-0763-4473-ba09-91398e938be7"] 7
["62b2b9c6-1991-48f7-8533-76fa877e9736" 0
"35b56ed8-74f0-42f8-ab1c-ede41605b7bd" 1
"71848241-6348-4fde-935e-74a5c369ede1" 2
"722f05a6-5672-4be6-902d-635372e04758" 3
"b2a45221-aafb-4949-8018-5fed6cf7c7fe" 4
"dbb49783-5f75-4b3c-a793-a933ea321679" 5
"6bb25395-6647-4668-9e6e-158ad5f0b8af" 6
"1b32e613-8e72-420f-b31f-7bc95650386c"] 7
["636e2084-fdeb-4594-a400-10f6ef2791d7" 0
"8ac273ab-b8b2-46af-a8b4-f8fb22afe8e4" 1
"372e4f00-4ce9-4a9f-927b-d34c5a4968c1" 2
"f821abb1-d97e-4d99-b630-f74de5d106c1" 3
"d40b9b64-e81d-4133-bde2-54975806c087" 4
"07937692-680f-4cb0-8d17-98684141b92c" 5
"3b2039d0-de86-4cd7-9fb2-21397932f14c" 6
"16c24542-65c8-45db-97dc-014e66db7ef0"] 7
["f67efbcc-898d-4b50-8c15-21ac4fbbc500" 0
"64c020c7-9bd1-4e00-968f-180e3d68e100" 1
"3667915a-8e10-41fb-8f00-035cc10324a6" 2
"b7bc23c3-f5a1-486c-a99a-6c61357ed000" 3
"11292acd-ef71-4e0c-8281-0f50007cf850" 4
"210cca62-61b4-4ed9-ad42-653a909d3045" 5
"1dd2468a-0a3c-43e6-aae6-bd702d1c8a74" 6
"d7f8e5ee-1e05-42e1-8ff4-89529a210a76"] 7
["a1de7674-fa0b-49e6-af6a-2522798c4861" 0
"5cca7cd7-f50b-4538-ad89-a85b2d72a555" 1
"30cd353c-ee8c-4a94-9fbd-166372c2fd96" 2
"7407ab86-fdf6-4bf1-8282-e1218e021ed3" 3
"20ce7593-b1e2-4401-9b7c-1af18ec37f6c" 4
"541e995a-1416-4f4b-9696-2827cdcbd64e" 5
"3247610e-0486-4891-8fce-32f2e03fcaec" 6
"6492db47-54af-4390-9c88-11f43c3eaef0"]] 7
The numbering needs to start from the beginning after every open and closed parentheses. It's half working as expected but how can I accomplish this?
Thank you for all your help!
Pete
I'm fairly new to PostgreSQL and have a situation where I need to flatten a text column to two separate columns.
Below is the query I'm using:
[CODE]SELECT s.CoolerShelf,
s.ShelfPosition ,
FROM planogram
CROSS JOIN LATERAL UNNEST(string_t o_array(shelves , ','))
WITH ORDINALITY s(CoolerShelf,S helfPosition)
code
code;
Below is the result set from the above query:
coolershelf shelfposition
[["8d2cf35d-5708-45e0-9cb6-acad358e0f92" 1
"5a91f7a2-029a-46d7-8440-9337dd1b87d3" 2
"521562a9-9d33-438d-8156-1e6b1874ec8e" 3
"e14817e4-6630-4dca-a188-ac71060dcac9" 4
"76967052-ba9d-43f5-afd4-b4bbe1452d7e" 5
"2e5a6fb2-071e-426b-ac55-69f16baa0b42" 6
"108f263d-ee78-4124-a94b-2c5641f90321" 7
"0dbe5016-9e78-4173-b6e6-ff3e0199ca2e"] 8
["9bd83b79-186d-4ae5-9373-956dbd515070" 9
"b6172191-fa44-436d-879d-c883e4d240ed" 10
"093b72ba-74cd-48b9-86df-7e7d9341ae53" 11
"88b6c7f8-1d23-4e82-b959-8cb3400cc039" 12
"8279d979-8a57-4595-b9d3-346f6b05924e" 13
"735e6139-0fce-4bb7-a4a2-00ceb86c9b07" 14
"0ad84c4f-e0d8-4606-b563-8b2e32cc632f" 15
"5a86f7ea-0763-4473-ba09-91398e938be7"] 16
["62b2b9c6-1991-48f7-8533-76fa877e9736" 17
"35b56ed8-74f0-42f8-ab1c-ede41605b7bd" 18
"71848241-6348-4fde-935e-74a5c369ede1" 19
"722f05a6-5672-4be6-902d-635372e04758" 20
"b2a45221-aafb-4949-8018-5fed6cf7c7fe" 21
"dbb49783-5f75-4b3c-a793-a933ea321679" 22
"6bb25395-6647-4668-9e6e-158ad5f0b8af" 23
"1b32e613-8e72-420f-b31f-7bc95650386c"] 24
["636e2084-fdeb-4594-a400-10f6ef2791d7" 25
"8ac273ab-b8b2-46af-a8b4-f8fb22afe8e4" 26
"372e4f00-4ce9-4a9f-927b-d34c5a4968c1" 27
"f821abb1-d97e-4d99-b630-f74de5d106c1" 28
"d40b9b64-e81d-4133-bde2-54975806c087" 29
"07937692-680f-4cb0-8d17-98684141b92c" 30
"3b2039d0-de86-4cd7-9fb2-21397932f14c" 31
"16c24542-65c8-45db-97dc-014e66db7ef0"] 32
["f67efbcc-898d-4b50-8c15-21ac4fbbc500" 33
"64c020c7-9bd1-4e00-968f-180e3d68e100" 34
"3667915a-8e10-41fb-8f00-035cc10324a6" 35
"b7bc23c3-f5a1-486c-a99a-6c61357ed000" 36
"11292acd-ef71-4e0c-8281-0f50007cf850" 37
"210cca62-61b4-4ed9-ad42-653a909d3045" 38
"1dd2468a-0a3c-43e6-aae6-bd702d1c8a74" 39
"d7f8e5ee-1e05-42e1-8ff4-89529a210a76"] 40
["a1de7674-fa0b-49e6-af6a-2522798c4861" 41
"5cca7cd7-f50b-4538-ad89-a85b2d72a555" 42
"30cd353c-ee8c-4a94-9fbd-166372c2fd96" 43
"7407ab86-fdf6-4bf1-8282-e1218e021ed3" 44
"20ce7593-b1e2-4401-9b7c-1af18ec37f6c" 45
"541e995a-1416-4f4b-9696-2827cdcbd64e" 46
"3247610e-0486-4891-8fce-32f2e03fcaec" 47
"6492db47-54af-4390-9c88-11f43c3eaef0"]] 48
The desired results should look like as follows:
coolershelf shelfposition
[["8d2cf35d-5708-45e0-9cb6-acad358e0f92" 0
"5a91f7a2-029a-46d7-8440-9337dd1b87d3" 1
"521562a9-9d33-438d-8156-1e6b1874ec8e" 2
"e14817e4-6630-4dca-a188-ac71060dcac9" 3
"76967052-ba9d-43f5-afd4-b4bbe1452d7e" 4
"2e5a6fb2-071e-426b-ac55-69f16baa0b42" 5
"108f263d-ee78-4124-a94b-2c5641f90321" 6
"0dbe5016-9e78-4173-b6e6-ff3e0199ca2e"] 7
["9bd83b79-186d-4ae5-9373-956dbd515070" 0
"b6172191-fa44-436d-879d-c883e4d240ed" 1
"093b72ba-74cd-48b9-86df-7e7d9341ae53" 2
"88b6c7f8-1d23-4e82-b959-8cb3400cc039" 3
"8279d979-8a57-4595-b9d3-346f6b05924e" 4
"735e6139-0fce-4bb7-a4a2-00ceb86c9b07" 5
"0ad84c4f-e0d8-4606-b563-8b2e32cc632f" 6
"5a86f7ea-0763-4473-ba09-91398e938be7"] 7
["62b2b9c6-1991-48f7-8533-76fa877e9736" 0
"35b56ed8-74f0-42f8-ab1c-ede41605b7bd" 1
"71848241-6348-4fde-935e-74a5c369ede1" 2
"722f05a6-5672-4be6-902d-635372e04758" 3
"b2a45221-aafb-4949-8018-5fed6cf7c7fe" 4
"dbb49783-5f75-4b3c-a793-a933ea321679" 5
"6bb25395-6647-4668-9e6e-158ad5f0b8af" 6
"1b32e613-8e72-420f-b31f-7bc95650386c"] 7
["636e2084-fdeb-4594-a400-10f6ef2791d7" 0
"8ac273ab-b8b2-46af-a8b4-f8fb22afe8e4" 1
"372e4f00-4ce9-4a9f-927b-d34c5a4968c1" 2
"f821abb1-d97e-4d99-b630-f74de5d106c1" 3
"d40b9b64-e81d-4133-bde2-54975806c087" 4
"07937692-680f-4cb0-8d17-98684141b92c" 5
"3b2039d0-de86-4cd7-9fb2-21397932f14c" 6
"16c24542-65c8-45db-97dc-014e66db7ef0"] 7
["f67efbcc-898d-4b50-8c15-21ac4fbbc500" 0
"64c020c7-9bd1-4e00-968f-180e3d68e100" 1
"3667915a-8e10-41fb-8f00-035cc10324a6" 2
"b7bc23c3-f5a1-486c-a99a-6c61357ed000" 3
"11292acd-ef71-4e0c-8281-0f50007cf850" 4
"210cca62-61b4-4ed9-ad42-653a909d3045" 5
"1dd2468a-0a3c-43e6-aae6-bd702d1c8a74" 6
"d7f8e5ee-1e05-42e1-8ff4-89529a210a76"] 7
["a1de7674-fa0b-49e6-af6a-2522798c4861" 0
"5cca7cd7-f50b-4538-ad89-a85b2d72a555" 1
"30cd353c-ee8c-4a94-9fbd-166372c2fd96" 2
"7407ab86-fdf6-4bf1-8282-e1218e021ed3" 3
"20ce7593-b1e2-4401-9b7c-1af18ec37f6c" 4
"541e995a-1416-4f4b-9696-2827cdcbd64e" 5
"3247610e-0486-4891-8fce-32f2e03fcaec" 6
"6492db47-54af-4390-9c88-11f43c3eaef0"]] 7
The numbering needs to start from the beginning after every open and closed parentheses. It's half working as expected but how can I accomplish this?
Thank you for all your help!
Pete