SQL query for duplicates in JSON array of objects

Example Data

const data = [
    {
        "Id": 1,
        "ActualUrl": "https://test.com/test",
        "DateCreated": "2024-08-11T08:44:01",
        "DateUpdated": "2024-08-11T08:44:01",
        "Deadline": "2024-08-12T08:44:01",
        "PullZoneId": 1,
        "PullZoneName": "1",
        "Path": "/test",
        "Message": "1",
        "Status": 0,
        "Urls": [
            { "Url": "https://test.com/", "Status": 0 },
            { "Url": "https://test.com/", "Status": 0 }
        ],
    },
    {
        "Id": 12
        "ActualUrl": "https://test.com/test",
        "DateCreated": "2024-08-11T08:44:01",
        "DateUpdated": "2024-08-11T08:44:01",
        "Deadline": "2024-08-12T08:44:01",
        "PullZoneId": 1,
        "PullZoneName": "1",
        "Path": "/test",
        "Message": "1",
        "Status": 0,
        "Urls": [
            { "Url": "https://test.com/", "Status": 0 },
            { "Url": "https://test.com/", "Status": 0 }
        ],
    },
    // Add more records as needed
];

I want to basically loop through Urls of all, add them together and find duplicates. I am unsure if its supported or if I am doing it wrong.

I tried using this query and a bunch of others but i always get an error or nothing shows up. I understand nesting is an option but I am working with raw JSON from an API so I can’t modify it much.

SELECT u.Url, COUNT(*) AS Count
FROM AbuseCases a
JOIN a.Urls u
GROUP BY u.Url
HAVING COUNT(*) > 1;