Feature: Support hierarchical data with optimised methods

[rad-pat]

It would be great to have some optimised functions to process hierarchical data rather than having to build recursive CTEs to get information about trees of data - siblings/children etc.

This link provides information about one particular implementation, but it could perhaps be done differently https://help.sap.com/docs/SAP_HANA_PLATFORM/4f9859d273254e04af6ab3e9ea3af286/9aa2f6d912f24dc5baca772bd40745fc.html?locale=en-US&version=2.0.05

[inviscid]

There does appear to be some discussion about handing nested/hierarchical data in Arrow and Parquet here: https://arrow.apache.org/blog/2022/10/08/arrow-parquet-encoding-part-2/

This would be a very nice feature to have since most "business" data includes some form of organizational layout like accounts, cost centers, departments, products, etc... Often times this data is jagged which makes using a predetermined number of table columns problematic.

The ability to quickly find ancestors, descendants, siblings, leaves, etc... without having to run recursive CTEs would be a very welcome capability.

[rad-pat]

There does appear to be some discussion about handing nested/hierarchical data in Arrow and Parquet here: https://arrow.apache.org/blog/2022/10/08/arrow-parquet-encoding-part-2/

I think this is referring to hierarchical data like JSON structure which I believe is a VARIANT column in Databend, not actually building a tree structure from parent-child or level relationships within a table of data.

To have the ability to create a TREE structure from a table and to then use the speed of RUST to get answers to the ancestors, descendants, siblings, leaves questions would be great. Perhaps the usage could be a little similar to a CTE.

e.g. sudo code

with TREE(table, parent_col, child_col) as t
select * 
from data_table d
where d.tree_node_col in siblings(t, node1, node2);

[b41sh]

It looks a bit similar to the json_path function, we can consider supporting these functions, it should be useful.

[sundy-li]

SAP supports CREATE COLUMN TABLE

[rad-pat]

It looks a bit similar to the json_path function, we can consider supporting these functions, it should be useful.

I wonder if I can leverage json_path functions for what we need in the interim, are those functions fully implemented @b41sh ?

[rad-pat]

I wonder if I can leverage json_path functions for what we need in the interim, are those functions fully implemented @b41sh ?

For now it seems that I cannot get the answers I desire using json_path because a recursive descent operator is not yet available. I raised an issue on that in the JSONB repo, I hope that is the correct place.

[b41sh]

Hi @rad-pat
PR #17698 support JsonPath recursive wildcard member accessor .** syntax, which can be used to access all levels of hierarchy object and array values. I hope this feature will be useful for you.

for example:

root@0.0.0.0:48000/default/default> SELECT json_path_query(parse_json('{"a":{"b":[1,2]}, "c":1}'),'$.**');
╭─────────────────────────────────────────────────────────────────╮
│ json_path_query(parse_json('{"a":{"b":[1,2]}, "c":1}'), '$.**') │
│                        Nullable(Variant)                        │
├─────────────────────────────────────────────────────────────────┤
│ {"a":{"b":[1,2]},"c":1}                                         │
│ {"b":[1,2]}                                                     │
│ [1,2]                                                           │
│ 1                                                               │
│ 2                                                               │
│ 1                                                               │
╰─────────────────────────────────────────────────────────────────╯
6 rows read in 0.033 sec. Processed 1 rows, 1 B (30.3 rows/s, 30 B/s)

root@0.0.0.0:48000/default/default> SELECT json_path_query(parse_json('{"a":{"b":[1,2]}, "c":1}'),'$.**{2}');
╭────────────────────────────────────────────────────────────────────╮
│ json_path_query(parse_json('{"a":{"b":[1,2]}, "c":1}'), '$.**{2}') │
│                          Nullable(Variant)                         │
├────────────────────────────────────────────────────────────────────┤
│ [1,2]                                                              │
╰────────────────────────────────────────────────────────────────────╯
1 row read in 0.020 sec. Processed 1 row, 1 B (50 rows/s, 50 B/s)

root@0.0.0.0:48000/default/default> SELECT json_path_query(parse_json('{"a":{"b":[1,2]}, "c":1}'),'$.**{2 to last}');
╭────────────────────────────────────────────────────────────────────────────╮
│ json_path_query(parse_json('{"a":{"b":[1,2]}, "c":1}'), '$.**{2 to last}') │
│                              Nullable(Variant)                             │
├────────────────────────────────────────────────────────────────────────────┤
│ [1,2]                                                                      │
│ 1                                                                          │
│ 2                                                                          │
╰────────────────────────────────────────────────────────────────────────────╯
3 rows read in 0.033 sec. Processed 1 rows, 1 B (30.3 rows/s, 30 B/s)

[rad-pat]

Thanks @b41sh
I'll.give it a good test when I'm back from vacation. I've got some fairly large JSON files to test with. If this works well, it will be extremely useful. Thanks

[rad-pat]

Is there some way to build JSON from query results? For example if we have data in a parent-child table.

CREATE TABLE t_tree (
id int not null,
parent int null,
name string not null
)

Insert into t_tree values (
(1, null, 'root'),
(2, 1, 'a'),
(3, 2, 'b'),
(4, 1, 'c'),
(5, 4, 'd')
)

Query to get JSON similar to this below
{
"root": [
"a": ["b"],
"c": ["d"]
]
}

[rad-pat]

Hi @b41sh, @BohuTANG. The introduction of the recursive descent operator for JSON does not resolve this issue in my opinion, it is only a part of the solution, see #17271 (comment).
I can't see how I am able to build the JSON from a parent-child record structure to then use the recursive descent operator to locate the items in question. Is there any way to do this that I am missing?