Property cannot be found on the specified JSON path

Question

I'm at a loss as to why I can't return the "id" value within the JSON. What reference am I missing considering the total_pages select returns just fine.

DECLARE @json NVARCHAR(MAX);

SET @json = N'{
  "data": [
    {
      "id": 1450
    },
    {
      "id": 1451
    }
],
  "total_pages": 1
 }
';

SELECT 
    *
FROM 
    OPENJSON(@json) WITH (
      id INT 'strict $.total_pages'
)

SELECT 
    *
FROM 
    OPENJSON(@json) WITH (
      id INT 'strict $.data.id'
)

Answer 1

When using OPENJSON in getting array object you need to specify:

1. $.data to parse the data array, in OPENJSON like => OPENJSON(@json, '$.data').

2. The WITH clause specifies the columns to be extracted from each object in the data array like:

   WITH ( id INT '$.id' );

so your query should look something like:

SELECT 
    id
FROM 
    OPENJSON(@json, '$.data')
    WITH (
      id INT '$.id'
    );

Now you may ask why not do the same for totalPages as well thats because: The OPENJSON function is used with the path '$' to parse the root-level properties and in your JSON object total_pages is already a root level property, so you dont need to specify its path. and as WITH clause specifies only columns to be extracted. In this case, total_pages is a column at root so thats why your underlying code works perfectly:

SELECT 
    *
FROM 
    OPENJSON(@json) WITH (
      id INT 'strict $.total_pages'
)