support get_json_array

[hongkunxu]

{
"people": [{
"name": "Daniel",
"surname": "Smith"
}, {
"name": "Lily",
"surname": "Smith",
"active": true
}]
}
For example, I have a JSON field in this format, and I want to query for rows where the user's name is "jack." However, there is no function like get_json_array available. Although I can extract the result using $[*].name, there's currently no way to convert this result into an array.
If the community finds this reasonable, I might try implementing this method, get_json_array. @wangsimo0

[hongkunxu]

Note: This issue is related to #52585.

[wangsimo0]

Thanks! could you pls give a demo and description about how you want to implement this? like the function's name, input, output and examples. just want to make sure we are on the same page. also some reference will be even better.
here is an example #36245

[jaogoy]

Use $.people[*].name not $[*].name.

select js->'$.people[*].name'
from (
    select parse_json('
    {
        "people": [{
            "name": "Daniel",
            "surname": "Smith"
        }, {
            "name": "Lily",
            "surname": "Smith",
            "active": true
        }]
    }') as js) t

Here are more examples.

You even can case a complex JSON array to an Array.

select cast(parse_json('[{"a":1}, {"a":2, "b":3}]') as Array<JSON>) as array_json

But, it's not test well. I don't know it works before.

However, if you want to do more complex filter on JSON array with some Array functinos, maybe it's not easy. You can give more real cases to have a test.

[hongkunxu]

Thanks @jaogoy . Your guide have solved my problems. And one more question, don't you know some function to solve below issue?
I have two array: array1: [1, 2, 3], array2: [2, 3]
Check if array1 contains any element from array2.
We all know array_contains(param1, param2), but param2 could't be array.

[hongkunxu]

array_length(array_intersect) could solve my problem.
Do you have more smarter method? @jaogoy