Adding column(s) inside nested object columns

Question

I've been trying to add a column in an object column inside another object column but not able to do so (for nested objects).

Adding columns

Adding a column inside an object is straight forward. How to add column inside nested object which is one level deep (or N levels deep) ?

create table my_table (name string, age integer, book object as (isbn string));

example row: {"age": 34, "book": {"isbn": "1"}, "name": "my_name"}

I have tried to add an object column 'author' inside book object column but the following alter statements fail

alter table my_table add column person['book['author']'] object as (authorId as integer)
alter table my_table add column person['book']['author'] object as (authorId as integer)
alter table my_table add column person['book[author]'] object as (authorId as integer)
alter table my_table add column person[book['author']] object as (authorId as integer)

What is the correct syntax for adding column(s) inside nested objects ?

Kind Regards.

Answer 1

Nested objects are accessed in this form:

select obj['level1']['level2']['level3']

and so forth.

So the alter table statement would look like your second example, but without the "as integer":

alter table my_table add column book['person']['author'] object as ("authorId" integer);

(note that the authorId is in double quotes to preserve the case, without it it would became all lowercase)

Or the same could also be accomplished with a slightly different syntax:

alter table my_table add column book['person']['author']['authorId'] integer;