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?

Does the outer array ever have more than one object in it, per row? — Charlieface, May 22 '23 at 21:24
@Charlieface yes it has a lot (+500), I copy only one for here — mrbangybang, May 22 '23 at 21:26
@mrbangybang Try json_value(YourJSON,'$[0].UserOrUserGroup.id') — John Cappelletti, May 22 '23 at 21:27
Looking at Charlie's question, you may need to look into OPENJSON() — John Cappelletti, May 22 '23 at 21:28
Be careful with your date predicates. Date and datetime datatypes can be a little misleading sometimes. When you use 2023-05-01 it is NOT going to be the date you think. It will instead be July 11, 1905 which is 2017 days after 1/1/1900. That is the result of the integer equation 2023-05-01. You need to use single quotes around date literals, and ideally in YYYYMMDD format. So something like >= '20230501'. — Sean Lange, May 22 '23 at 21:36

score 1 · Answer 1 · answered May 22 '23 at 21:34

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.

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

1 Answers1