I have a nested json something like below.
For each area I want to extract rate from properties where my Cat is 1 and class is A or B.
I want to insert area, pin , rateA, rateB in table 1 and entire properties section with areas and pin in table l2. How can this be achieved without iterating through properties or json array.
There’s a new function in 7.1, coming very soon, that can exploded an array into a set of rows. You can use it with JSON_TO_ARRAY to explode a JSON array to a set of rows. The documentation should be published shortly. Here’s some information about it.
Syntax
TABLE(<array_value>)
Behavior
When used in the FROM clause by itself, it produces a table with one element for each array entry.
When used in the FROM clause as a second or subsequent entry, there is an implicit LATERAL before the function, as there is in the SQL standard for JSON_TABLE.
When used as the second or subsequent entry in the FROM clause, with an argument that is correlated (from a table variable to the left), there will be N rows of output if the array value passed to TABLE() has N entries. The values from the table to the left will be repeated. The values from the array will be filled in with one array entry per output row. E.g.
Create table t(id int, json_col json);
insert into t values(1, ‘[1,2,3]’);
Select * from t JOIN TABLE(json_to_array(t.json_col));
Result:
id
json_col
table_col
1
[1,2,3]
1
1
[1,2,3]
2
1
[1,2,3]
3
You might be able to use it in a cascading sequence to explode the top level array, then the nested one. Otherwise, you may have to iterate through it in a stored procedure or in your app. You can try the 7.1 public beta. The feature is in the code.