Can I use dot navigation when using multiple parameters to filter (compare) in SQL SELECT?

Question

I new in SQL, I want to retrieve only the records of a certain groupID and I am using this query:

SELECT @Date, Capacity, UserOrUserGroup 
FROM UCTimePhaseMonthly 
WHERE Date >= 2023-05-01 
  AND UserOrUserGroup.id = '/User/fdwr6cs6msxapw5zh4bh5jj93'

Above an example of just one return object.

 [
    {
        "id": "/UCTimePhaseMonthly/2z1r4vdk3d8qk16uegexx5rxx3453",
        "Date": "2023-06-01T00:00:00.0000000",
        "Capacity": {
            "unit": "Hours",
            "value": 176
        },
        "UserOrUserGroup": {
            "id": "/User/fdwr6cs6msxapw5zh4bh5jj93"
        }
    }    
 ]

If the field id is nested inside UserOrUserGroup, can I use dot navigation when using multiple parameters to filter (compare) in SQL SELECT?

Answer 1

Since you have an array, you need to use OPENJSON. You can do it inside an EXISTS

SELECT
  @Date,
  tpm.Capacity,
  tpm.UserOrUserGroup
FROM UCTimePhaseMonthly tpm
WHERE Date >= '20230501'
  AND EXISTS (SELECT 1
    FROM OPENJSON(tpm.UserOrUserGroup)
      WITH (
        id varchar(100) '$.UserOrUserGroup.id'
      ) j
    WHERE j.id = '/User/fdwr6cs6msxapw5zh4bh5jj93'
);

Or you can pull out the actual value

SELECT
  @Date,
  tpm.Capacity,
  tpm.UserOrUserGroup,
  j.id
FROM UCTimePhaseMonthly tpm
CROSS APPLY OPENJSON(tpm.UserOrUserGroup)
WITH (
  id varchar(100) '$.UserOrUserGroup.id'
) j
WHERE Date >= '20230501'
  AND j.id = '/User/fdwr6cs6msxapw5zh4bh5jj93';

Note that dates need to go in quotes.