OPENJSON to Parse Field getting Subquery returned more than 1 value error-CodePudding

I have an object in a database which I believe can be parsed using OPENJSON. If there is a better solution, I am definitely open to it. The values below are fictional.

TABLE

 DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp (
ID INT,
Object VARCHAR(MAX)
);

INSERT INTO #temp
(
    ID,
    Object
)
VALUES
(   1, '{   "Country": "US",   "CountryName": "United States of America",   "Inflation": 5.0 }'),
(   2, '{   "Country": "MX",   "CountryName": "Mexico",   "Inflation": 6.0 }'),
(   3, '{   "Country": "CA",   "CountryName": "Canada,   "Inflation": 5.5 }');

ATTEMPTED SOLUTION

SELECT *
  FROM OPENJSON((SELECT Object FROM #temp))
  WITH (
         Country CHAR(2) '$.Country',
         CountryName VARCHAR(MAX) '$.CountryName',
         Inflation DECIMAL(2,1) '$.Inflation'
       )

RESULT

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

DESIRED RESULT

ID	Country	Country Name	Inflation
1	US	United States of America	5.0
2	MX	Mexico	6.0
1	CA	Canada	5.5

Any assistance would be greatly appreciated!

CodePudding user response：

After you add the " after Canada ... Use a CROSS APPLY

Select A.ID 
      ,B.*
 From  #temp A
 Cross Apply (
            SELECT *
              FROM OPENJSON(Object)
              WITH (
                     Country CHAR(2) '$.Country',
                     CountryName VARCHAR(MAX) '$.CountryName',
                     Inflation DECIMAL(2,1) '$.Inflation'
                   )     
             ) B

Results

EDIT

OR - If your JSON is that simple, the following is a nudge more performant.

 Select A.ID 
      ,Country     = JSON_VALUE(Object,'$.Country')
      ,CountryName = JSON_VALUE(Object,'$.CountryName')
      ,Inflation   = JSON_VALUE(Object,'$.Inflation')
 From  #Temp A