How to select data from SQL JSON when array has an array within?

Question

I have a table like below:

And the Data field is like this:

[
  {
    "MusicGenreId": 0,
    "Dates": [
      "2020-01-03T09:57:48.8476051Z",
      "2020-03-16T09:57:48.8476059Z",
      "2020-04-27T09:57:48.847606Z",
      "2020-02-01T09:57:48.8476061Z"
    ]
  },
  {
    "MusicGenreId": 1,
    "Dates": [
      "2020-01-04T09:57:48.8476065Z",
      "2020-06-11T09:57:48.8476066Z",
      "2020-02-26T09:57:48.8476067Z"
    ]
  }
]

I want to select DISTINCT UserId from this table by given between dates like:

SELECT 
    DISTINCT UserId
FROM
    UserProfile
WHERE
    exists
    (
        SELECT value FROM OPENJSON([Data])
        WHERE
            @StartDate <= value 
            AND value <= @EndDate
    )

I know this SQL is wrong according to my JSON structure and I've tried many things. I posted this SQL query just to simply explain what I need.

I'd so happy if you could help me out with this.

Thanks.

Answer 1

I finally found my answer thanks to melkisadek who posted a clue in the comment section. Here is the final query:

SELECT 
    DISTINCT UserId
FROM
    [Account].[UserProfile]
WHERE
    [DataTypeId] = @DataTypeId
    AND exists
    (
        SELECT TOP 1
            APLD.JobDate
        FROM
            OPENJSON([Data]) d
            CROSS APPLY OPENJSON(d.value, '$.Dates') 
            WITH(JobDate DATETIME2 '$') APLD 
        WHERE
            APLD.JobDate BETWEEN @StartDate AND @EndDate
    )

Answer 2

Probably it`s logically incorrect way to select UserId by those fields. I would advice You to rethink application logic and flow to find different approach if possible. Or redesign db structure if You need to get user who was probably listening music genres between given dates.