T-SQL: Query a Json column for records where an array element is missing a value

Question

In SQL Server 2016, I have a Shipments table like this:

create table Shipments 
(
    shipment_number varchar(20),
    shipment_json nvarchar(MAX)
);

shipment_json column contents is like this:

{
    "shipmentNumber": "008863",
    "stops": [
        {
            "locations": [
                {
                    "stopRole": "originPort",
                    "closeDateTime": "2023-08-22T05:00:00"
                },
                {
                    "stopRole": "destinationPort"
                }
            ]
        }
    ]
}

Now, I please need a SELECT query for records where, in the "locations" array, the "location" element with stopRole = “destinationPort” is missing the closeDateTime value. The json sample above is an example of that.

I appreciate any help.

Answer 1

Using openjson through cross apply enables treating the stopRole & closeDateTime as if they are typical columns e.g:

SELECT
      shipment_number
    , stopRole
    , closeDateTime
FROM Shipments
CROSS APPLY OPENJSON(shipment_json, '$.stops')
    WITH (
        locations nvarchar(MAX) '$.locations' AS JSON
    ) AS Stops
CROSS APPLY OPENJSON(Stops.locations)
    WITH (
        stopRole nvarchar(50) '$.stopRole',
        closeDateTime nvarchar(50) '$.closeDateTime'
    ) AS Locations
WHERE Locations.closeDateTime IS NULL

shipment_number	stopRole	closeDateTime
008863	destinationPort	null

fiddle