SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

with ENTITIES as (
select [name] as entityName from (values ('A'), ('B'), ('C')) X([name])
),
PROPERTIES as (
select [propName] as entityName, [propValue] as propertyValue from (values ('A','1'),('A','2'),('B','3'),('UNIVERSAL','999')) Y([propName], [propValue])
),
SUBPROPERTIES as (
select [propValue] as propertyValue, [subPropValue] as subPropertyValue from (values ('1','x'),('1','y'),('2','z'),('999','xyz')) Y([propValue], [subPropValue])
)

select entityName as 'entityName',
JSON_QUERY(
	(select propertyValue, JSON_QUERY(Y) AS subPropertyValue from 
		(
			select propertyValue as 'propertyValue',
			(
				select subPropertyValue from SUBPROPERTIES where SUBPROPERTIES.propertyValue = PROPERTIES.propertyValue
				FOR JSON PATH, INCLUDE_NULL_VALUES
			) Y
			from PROPERTIES where PROPERTIES.entityName = ENTITIES.entityName

-- For testing, comment out from here -------------------------------------------------------------------------------------
			UNION

select propertyValue as 'propertyValue',
			(
				select subPropertyValue from SUBPROPERTIES where SUBPROPERTIES.propertyValue = PROPERTIES.propertyValue
				FOR JSON PATH, INCLUDE_NULL_VALUES
			) Y
			from PROPERTIES where PROPERTIES.entityName = 'UNIVERSAL' 
-- For testing, stop commenting out here ----------------------------------------------------------------------------------

) X
	FOR JSON PATH, INCLUDE_NULL_VALUES
)
) as entityProperties
from ENTITIES
FOR JSON PATH, INCLUDE_NULL_VALUES

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
[{"entityName":"A","entityProperties":[{"propertyValue":"1","subPropertyValue":[{"subPropertyValue":"x"},{"subPropertyValue":"y"}]},{"propertyValue":"2","subPropertyValue":[{"subPropertyValue":"z"}]},{"propertyValue":"999","subPropertyValue":[{"subPropertyValue":"xyz"}]}]},{"entityName":"B","entityProperties":[{"propertyValue":"3","subPropertyValue":null},{"propertyValue":"999","subPropertyValue":[{"subPropertyValue":"xyz"}]}]},{"entityName":"C","entityProperties":[{"propertyValue":"999","subPropertyValue":[{"subPropertyValue":"xyz"}]}]}]

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[{"entityName":"A","entityProperties":[{"propertyValue":"1","subPropertyValue":[{"subPropertyValue":"x"},{"subPropertyValue":"y"}]},{"propertyValue":"2","subPropertyValue":[{"subPropertyValue":"z"}]},{"propertyValue":"999","subPropertyValue":[{"subPropertyValue":"xyz"}]}]},{"entityName":"B","entityProperties":[{"propertyValue":"3","subPropertyValue":null},{"propertyValue":"999","subPropertyValue":[{"subPropertyValue":"xyz"}]}]},{"entityName":"C","entityProperties":[{"propertyValue":"999","subPropertyValue":[{"subPropertyValue":"xyz"}]}]}]