I have some query generated by EF Core 9. EF uses OPENJSON function on MS SQL SERVER where you use Contains in EF Core query. With generated query where OPENJSON is used in SUBQUERY The SQL Server evaluates it as many times as joined/filtered rows. That is very bad. A query from the example finishes in almost **4 seconds**. When I replace it with WHERE IN then it finishes in **~50 ms**. Unfortunately sometimes that OPENJSON contains a lot of items so I can not replace that permanently. Is there any solution to tell SQL Server to evaluate this OPENJSON only once? -- or -- Is it a BUG in MS SQL Server 2019?

Earlier today, I needed to put some small data quickly in a SQL Server database: CREATE VIEW MyView AS SELECT * FROM OPENJSON('[ ... ]') WITH ( colA int, etc. ); intending to go back and replace this with a real CREATE TABLE, INSERT INTO, etc. at some point. SQL Server isn't doing anything smart to cache or store this data, is it? It is parsing the JSON every time the view is queried, right? Just wanted to make sure before I replace it.

I have these JSON: Application 1: { "business_industry": "Agriculture", "docs": [ { "internal": false, "type": "Asset & Liability Statement" }, { "internal": false, "name": "Privacy Consent", "type": "Privacy Consent" } ], "quote": { "principal": 0, "short_id": "3856545" } } Application 2: { "business_industry": "Construction", "docs": [ { "internal": false, "name": "Privacy Consent", "type": "Privacy Consent" } ], "asset": { "model": null, "make": "3856545" } } Application 3: { "business_industry": "Coal Mining", "business_business_names": [ { "business_organisation_name": "Centron Consulting Services", "business_effective_from": "2018-04-11" } ], "lite_doc": { "total_sales": 0, "total_sales2": 0, "refunds_present": false, "payment_arrangement_evident": false } } I would like to **query all applications** and get the **keys with objects as values, automatically**. Because I will use those keys as **references for creating new models** in another database. Something like: +----------+-----------+-------+----------------------------+-----------------------------+ | docs | quotes | asset | business_business_names | lite_doc | +----------+-----------+-------+----------------------------+-----------------------------+ | internal | principal | model | business_organisation_name | total_sales | | type | short_id | make | business_effective_from | total_sales2 | | name | | | | refunds_present | | | | | | payment_arrangement_evident | +----------+-----------+-------+----------------------------+-----------------------------+ I will then create five models: docs, quotes, asset, business_business_names, and lite_doc which have properties listed above. The objects can either be another dictionary or an array. This code is currently what I have: WITH docs AS ( SELECT docs = STRING_AGG(j.[key], ' ') FROM ( SELECT DISTINCT j.[key] FROM Application a1 CROSS APPLY OPENJSON(a1.DataReceived, '$.docs') j0 CROSS APPLY OPENJSON(j0.value) j ) j ), quotes AS ( SELECT quotes = STRING_AGG(j.[key], ' ') FROM ( SELECT DISTINCT j.[key] FROM Application a1 CROSS APPLY OPENJSON(a1.DataReceived, '$.quote') j ) j ), asset AS ( SELECT asset = STRING_AGG(j.[key], ' ') FROM ( SELECT DISTINCT j.[key] FROM Application a1 CROSS APPLY OPENJSON(a1.DataReceived, '$.asset') j ) j ), business_business_names AS ( SELECT business_business_names = STRING_AGG(j.[key], ' ') FROM ( SELECT DISTINCT j.[key] FROM Application a1 CROSS APPLY OPENJSON(a1.DataReceived, '$.business_business_names') j0 CROSS APPLY OPENJSON(j0.value) j ) j ), lite_doc AS ( SELECT lite_doc = STRING_AGG(j.[key], ' ') FROM ( SELECT DISTINCT j.[key] FROM Application a1 CROSS APPLY OPENJSON(a1.DataReceived, '$.lite_doc') j ) j ) SELECT * FROM docs CROSS JOIN quotes CROSS JOIN asset CROSS JOIN business_business_names CROSS JOIN lite_doc; but if I add a new key-value pair where the value is either an object or array, I'd have to add another query as well. How do I do it **automatically** and catching DataReceived values that are **not objects**?

I have these JSON: Application 1: { "business_industry": "Agriculture", "docs": [ { "internal": false, "type": "Asset & Liability Statement" }, { "internal": false, "name": "Privacy Consent", "type": "Privacy Consent" } ], "quote": { "principal": 0, "short_id": "3856545" } } Application 2: { "business_industry": "Construction", "docs": [ { "internal": false, "name": "Privacy Consent", "type": "Privacy Consent" } ], "asset": { "model": null, "make": "3856545" } } Application 3: { "business_industry": "Coal Mining", "business_business_names": [ { "business_organisation_name": "Centron Consulting Services", "business_effective_from": "2018-04-11" } ], "lite_doc": { "total_sales": 0, "total_sales2": 0, "refunds_present": false, "payment_arrangement_evident": false } } which are derived from DataReceived in my Application model. I would like to **query all applications** and get the **keys with objects as values**. Because I will use those keys as **references for creating new models** in another database. Something like: +----------+-----------+-------+----------------------------+-----------------------------+ | docs | quotes | asset | business_business_names | lite_doc | +----------+-----------+-------+----------------------------+-----------------------------+ | internal | principal | model | business_organisation_name | total_sales | | type | short_id | make | business_effective_from | total_sales2 | | name | | | | refunds_present | | | | | | payment_arrangement_evident | +----------+-----------+-------+----------------------------+-----------------------------+ I will then create five models: docs, quotes, asset, business_business_names, and lite_doc which have properties listed above. The objects can either be another dictionary or an array. This code is currently what I have: BEGIN TRANSACTION; BEGIN TRY SELECT asset.[key] AS asset FROM dbo.Application -- SELECT ALL KEYS THAT HAVE OBJECTS AS VALUES AND DISPLAY THOSE OBJECTS' KEYS CROSS APPLY OPENJSON(DataReceived, '$.asset') AS asset; END TRY BEGIN CATCH -- Do nothing END CATCH ROLLBACK TRANSACTION; But it can only query the asset and it's not able to do DISTINCT. For some reason DISTINCT does not work in the query.