Ufdjrw

I'm having issues mapping a json string returned from sql.

The structure of the object looks like this

 public class OrderSummaryDto
 
 public List<PrescriptionInfo> PrescriptionsInfo get; set; 

 public class PrescriptionInfo
 
 public string Name get; set; 
 public AdministrationInfo Administration get; set; 

 public class AdministrationInfo
 
 public string Instructions get; set; 
 public string PackageType get; set; 
 
 
 

The json string returned from DB

["Name":"testName","Units":3,"Medium":"flower power","Administration":""Instructions":"drIVnkLEm0b24OK9ceMeeF2fq","PackageType":"case"","Name":"testName","Units":3,"Medium":"flower power","Administration":""Instructions":"drIVnkLEm0b24OK9ceMeeF2fq","PackageType":"case""]

The Sql that generates the json string

(SELECT _co.[Name] 
 ,_co.[Pharmacy_Instructions] AS [Administration]
 FROM [dbo].[Compounds] _co
 WHERE _co.[Id] = 1
 FOR JSON PATH) AS [PrescriptionsInfo] 

Pharmacy_Instructions is already a JSON formated string

Message "Could not cast or convert from System.String to
Models.Order.Summary.OrderSummaryDto+PrescriptionInfo+AdministrationInfo." string

Your JSON string is currently a list of PrescriptionInfo Objects, not a OrderSummary object that contains a list of PrescriptionInfo objects. In addition, some fields (Administration) are being treated as strings, instead of objects. The JSON needs to look like this to work properly:

"PrescriptionsInfo": ["Name":"testName","Units":3,"Medium":"flower power","Administration":"Instructions":"drIVnkLEm0b24OK9ceMeeF2fq","PackageType":"case","Name":"testName","Units":3,"Medium":"flower power","Administration":"Instructions":"drIVnkLEm0b24OK9ceMeeF2fq","PackageType":"case"]

Or formatted:

 
 "PrescriptionsInfo":[ 
 
 "Name":"testName",
 "Units":3,
 "Medium":"flower power",
 "Administration": 
 "Instructions":"drIVnkLEm0b24OK9ceMeeF2fq",
 "PackageType":"case"
 
 ,
 
 "Name":"testName",
 "Units":3,
 "Medium":"flower power",
 "Administration": 
 "Instructions":"drIVnkLEm0b24OK9ceMeeF2fq",
 "PackageType":"case"
 
 
 ]

In order to do this you will have to use aliases. Check out this MS guide
https://docs.microsoft.com/en-us/sql/relational-databases/json/format-nested-json-output-with-path-mode-sql-server?view=sql-server-2017

I am weak with JSON support in SQL, might ask with those tags, but it seems like it would be something similar to this.

(SELECT _co.[Name], 
 JSON_VALUE(_co.[Pharmacy_Instructions], '$.Instructions') AS "Administration.Instructions",
 JSON_VALUE(_co.[Pharmacy_Instructions], '$.PackageType') AS "Administration.PackageType"
 FROM [dbo].[Compounds] _co
 WHERE _co.[Id] = 1
 FOR JSON PATH) AS [PrescriptionsInfo]