Doing a a join within the same row in SQL

qwerty1 — Mon, 24 Apr 2023 08:56:26 GMT

My data is a dump of JSON response from an API. The schema of the json is

col_name  data_type
 
data           array<struct<attributes:struct<name: String, age: Int relationships:struct<address:struct<data:arraay<struct<id: long, type: string>>>>>>>
 
included    array<struct<id: long, type: string, attributes:struct<address: string, postalCode: string, country: string>>>

As you can see the column data contains an array of person details and includes a relationship to that person's address via an id. The column included contains the the actual address.

I want to transform this data into a new table where the person data includes the address. In short I want to get rid of this `included` business. I only have SQL to go with right now because I am using this in a STREAMING LIVE TABLE query.

Re: Doing a a join within the same row in SQL

qwerty1 — Tue, 25 Apr 2023 03:35:36 GMT

@Kaniz Fatma isn't this basically doing an "explode" on "data" and "included" and then joining them? We end up doing join on the whole data set instead of within the row.

Re: Doing a a join within the same row in SQL

qwerty1 — Wed, 26 Apr 2023 07:00:19 GMT

I used a similar solution (exploding only one column) and it worked

topic Doing a a join within the same row in SQL in Data Engineering

Doing a a join within the same row in SQL

Re: Doing a a join within the same row in SQL

Re: Doing a a join within the same row in SQL