I have data structured like so, where there is a column of JSON strings. In each of these JSON strings, there are zero or more key/value pairs. A key could have any name, and a value is an array of at least one string

--------------------------------------------------------------------
|   Name   | Age | JsonCities                                      |
--------------------------------------------------------------------
|   Alice  | 30  | {"TX":["Houston","Dallas"],"AZ":["Phoenix"]}    |
|   Bob    | 45  | {"FL":["Tampa"]}                                |
|   Carol  | 38  | {}                                              |

I need to expand the JSON column so the data is structured as such, with each pair of state and city getting a row. Carol can be excluded because she has no location data, but if that’s difficult to include, I can filter out the null values after

------------------------------------
|   Name   | Age | State |  City   |
------------------------------------
|   Alice  | 30  |  TX   | Houston |
|   Alice  | 30  |  TX   | Dallas  |
|   Alice  | 30  |  AZ   | Phoenix |
|   Bob    | 45  |  FL   | Tampa   |

I’m using Microsoft Fabric with Azure SQL Data Warehouse version 12.0.2000.8 and a compatibility_level of 160

How can I accomplish this, whether using OPENJSON or another method?

I’ve been trying to solve this using OPENJSON but I can’t seem to make it work for my situation. When I try following similar questions, like this one, using this code
Apply OPENJSON to a single column

SELECT T.[Name], T.[Age], JsonData.[key], JsonData.[value]
FROM users T CROSS APPLY OPENJSON (T.JsonCities) AS JsonData

I get this error

Table-valued function ‘OPENJSON_DEFAULT’ is not supported in this version of Synapse Sql

but I am able to run something simpler like this without error

SELECT * FROM OPENJSON('{}')