Is there a way to take a JSON object from a table and expand values out into individual columns usin-CodePudding

Lets say I have the following table with properties, property_address and property_revenue all of type VARIANT:

ID	properties	property_address	property_revenue
1	{...}	{...}	{...}
2	{...}	{...}	{...}

An example of a value in the properties column could be:

{
"address": {
    "value": "123 ABC"
  },
  "revenue": {
    "value": 1000000
  }
}

and for example the property_adress column for that row would only contain

{
    "value": "123 ABC"
}

What would be the easiest way to extract the data and create a table where only the "value" is displayed given the fact there are hundreds of these columns?

table

One possible idea I had was to use flatten() on the properties column in some shape or form but I'm not sure how I would go about doing that. Another way was to use python to loop across each column and then build an SQL query off of that. Do you guys have any suggestions?

CodePudding user response：

You want to use LATERAL and FLATTEN for this: https://community.snowflake.com/s/article/How-To-Lateral-Join-Tutorial

There are some other tutorial references where you can generate view definitions based on the JSON data - this derivative approach can be used to generate column headers from a dynamic query: https://community.snowflake.com/s/article/Automating-Snowflake-Semi-Structured-JSON-Data-Handling-part-2

CodePudding user response：

FLATTEN to turn arrays into rows. OR object members into rows.

What your data appears to be showing a is a couple nested objects. If the objects are consistent, you should just unpack them.

Which I see in the comments you, do not want to do.

I see this a lot recently, and find it strange. I will reframe this another way. A structured database has structure because the structure meets the needs of the problem being solved. And yet you would like to have your needs meet for the problem you would like to solve but you don't want define the structure of you data.