apache spark - Count of all dates in an array which are present between two other columns

Question

My JSON looks like this

{"array":["2021-01-17","2021-01-25"], "beginDate": "2021-01-01", "endDate": "2021-01-20"}

I want to find the count of all dates in the array which are present between beginDate and endDate.

I was trying to create a sequence of dates between begin and end date and doing an array_intersect. Similar to this:

select size(array_intersect(array("2021-12-02","2021-12-05","2021-12-10"),(sequence(to_date("2021-12-01"), date_add(to_date("2021-12-06"),-1), interval 1 day))))

But I am getting:

array_intersect should have been two arrays with same element type, but it's [array<string>, array<date>]

When I run it on my JSON.

So I believe I need to convert my array to array<date>. Any suggestions?

question from:https://stackoverflow.com/questions/65926552/count-of-all-dates-in-an-array-which-are-present-between-two-other-columns

Answer 1

You need to make sure that both arrays are array<date>. In your example, the first array was array<string>, which caused an error when you intersect it with an array<date>.

select 
    size(
        array_intersect(
            transform(array("2021-12-02","2021-12-05","2021-12-10"), x -> date(x)),
            sequence(to_date("2021-12-01"), date_add(to_date("2021-12-06"), -1), interval 1 day)
        )
    )
;

This query gives 2.

---

For your initial question, you can try this query:

select 
    size(
        array_intersect(
            transform(array, x -> date(x)),
            sequence(to_date(begin), date_add(to_date(end), -1), interval 1 day)
        )
    )
;