How to update the items in array of struct column with sql

guostong — Fri, 16 Jun 2023 21:27:16 GMT

create table test.json_test_01 (
	id int,
	description string,
	struct_address STRUCT<street_number: STRING, street_name: STRING, city: STRING, province: STRING>,
	arrary_phone ARRAY<STRUCT<phone_number: STRING, phone_type: STRING>>
);
 
insert into test.json_test_01
select 1 as id,
       'first' as description,
       STRUCT('1001', 'street1', 'city1', 'province1') as struct_address,
       ARRAY(STRUCT('1000001', 'Home'), STRUCT('1000002', 'Cell'));
 
insert into test.json_test_01
select 2 as id,
       'second' as description,
       STRUCT('2001', 'street2', 'city2', 'province2') as struct_address,
       ARRAY(STRUCT('2000001', 'Home'), STRUCT('2000002', 'Work'));

we can update the property in struct column:

update test.json_test_01
set struct_address.street_number = '3001'
where id = 1;

but how to update the property in array of struct?

update test.json_test_01
set arrary_phone[*].phone_type = 'X'
where id = 2;

Re: How to update the items in array of struct column with sql

Anonymous — Sat, 17 Jun 2023 09:29:43 GMT

Hi @Richard Guo

Great to meet you, and thanks for your question!

Let's see if your peers in the community have an answer to your question. Thanks.

topic Re: How to update the items in array of struct column with sql in Data Engineering

How to update the items in array of struct column with sql

Re: How to update the items in array of struct column with sql