I tried to return this format from SQL Server using JSON_Query – I need your help.
[modifierGroups] = ISNULL((SELECT
@modifierGroups AS _id,
'General' AS [name],
'' AS [description],
[descriptionTranslations] = ISNULL((SELECT * FROM sys.tables WHERE 1 = 2
FOR JSON PATH ), '{}'),
[nameTranslations] = ISNULL((SELECT *
FROM sys.tables
WHERE 1 = 2
FOR JSON PATH ), '{}'),
'' AS account, --required
[capacityUsages] = ISNULL((SELECT *
FROM sys.tables
WHERE 1 = 2
FOR JSON PATH ), '{}'),
CAST(0 AS int) AS deliveryTax,
CAST(0 AS int) AS eatInTax,
CAST(0 AS int) AS takeawayTax,
'' AS [location],
CAST(0 AS int) AS [max],
CAST(0 AS int) AS [min],
CAST(1 AS int) AS [multiply],
@modifierGroups AS plu,
[posCategoryIds] = ISNULL((SELECT *
FROM sys.tables
WHERE 1 = 2
FOR JSON PATH ), '[]'),
'' AS posProductCategoryId,
'' AS posProductId,
[productTags] = ISNULL((SELECT *
FROM sys.tables
WHERE 1 = 2
FOR JSON PATH ), '[]'),
CAST(3 AS int) AS productType,
[subProducts] = ISNULL((SELECT *
FROM sys.tables
WHERE 1 = 2 --SELECT ir_item_remark_id FROM ItemRemarks
FOR JSON PATH ), '[]'),
'' AS parentId,
CAST(0 AS bit) AS snoozed,
[subProductSortOrder] = ISNULL((SELECT *
FROM sys.tables
WHERE 1 = 2
FOR JSON PATH ), '[]')
FOR JSON PATH ),'{}')
I need this format
"modifierGroups":{
"65********************31":{
"_id":"65********************31",
"name":"Cooking instructions",
"description":"",
"descriptionTranslations":{
},
"nameTranslations":{
"es":"Cocinado",
"fr":"Cuisson"
},
"account":"62********************f1",
"capacityUsages":[
],
"deliveryTax":9000,
"eatInTax":9000,
"takeawayTax":9000,
"location":"65********************32",
"max":1,
"min":1,
"multiply":1,
"plu":"MOD-01",
"posCategoryIds":[
],
"posProductCategoryId":"",
"posProductId":"",
"productTags":[
],
"productType":3,
"subProducts":[
"65********************32",
"65********************33",
"65********************34"
],
"parentId":"65********************30",
"snoozed":false,
"subProductSortOrder":[
]
},
2