Query: nosql_table_parents_for_nosql_table

with parent_name as (
  select
    split_part(c_name, '.', (array_length(string_to_array(c_name, '.'),1)-1)) as parent_table_name,
    c_name,
    id as child_id
  from (
    select
      name as c_name,
      id
    from
      oci_nosql_table
    ) as a
  where
    (array_length(string_to_array(c_name, '.'),1)-1) > 0
),
all_parent_name as (
  select
    split_part(p.c_name, p.parent_table_name, 1) || parent_table_name as parent_name,
    child_id
  from
    parent_name as p
)
select
  t.id as parent_table_id
from
  oci_nosql_table as t,
  all_parent_name as p
where
  t.name = p.parent_name
  and p.child_id = $1;