Query inserts more records than expected from a JSON file SQL Server

I am inserting data from a JSON file. In the JSON file, a key has 3 value items. The problem I have is that instead of inserting the 3 that it actually goes through with the cross aply, it inserts me 18 records (and not 3, one for cookie, one for third and other for log).

The whatsapp .json file is:

{
 "name_service": "WhatsApp",
 "source": "https://www.whatsapp .com/legal/?doc=version=20120707",
 "industry_name": "Unknow",
 "subindustry_name": "Unknow",
 "version_date": "27/01/2020",
 "category_track": [
    "Cookie",
    "Third",
    "Log"
  ]
}

And the SQL Server query I had was this:

Declare @JSON VARCHAR(max)
SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'C:UserDesktopwhatsapp.json', SINGLE_CLOB) import

INSERT INTO DATAS(pol_id, service_name, source, parsed_version_url, version_date, data)
select 4, service_name, source, parsed_version_url, version_date, data
  from OPENJSON (@JSON)
  cross apply openjson (@JSON) with (
         service_name VARCHAR (50) '$.name_service',
         source VARCHAR (50) '$.source',
         parsed_version_url VARCHAR (100) '$.source',
         version_date DATE '$.version_date'
         )
  cross apply openjson (@JSON, '$."category_track"') with (
         data varchar (50) '$'
         );

Answer

The reason for this unexpected result is the fact, that OPENJSON() with a default schema (without the WITH clause) returns rows for each first-level keyvalue pair (6 in your case) and the second APPLY operator with OPENJSON() and an explicit schema operator returns a row for each item in the $.category_track JSON array (3 in your case).

What you need is a different statement using AS JSON and only one APPLY operator. Also consider parsing the dates as varchar in the WITH clause and convert the parsed dates with TRY_CONVERT() and the correct date style:

INSERT INTO DATAS(pol_id, service_name, source, parsed_version_url, version_date, data)
select 4, service_name, source, parsed_version_url, TRY_CONVERT(date, version_date, 103) AS version_date, data
from OPENJSON (@json) with (
   service_name VARCHAR (50) '$.name_service',
   source VARCHAR (50) '$.source',
   parsed_version_url VARCHAR (100) '$.source',
   version_date varchar(10) '$.version_date',
   categories nvarchar(max) '$.category_track' AS JSON
) j1
cross apply openjson (j1.categories) with (
   data varchar (50) '$'
) j2