HITMAN: Agent 47 I want you find every available weapon on all of the maps in 5 minutes: JSON query, cross apply and string manipulation

Anybody who knows me know that I am a HITMAN enthusiast.

I love that the fact that it is a cerebral game.

Sneaking around, trying to manipulate the laws of the game is something I frequently do whilst I should be sleeping.

Ever wanted to know how many suppressed weapons there are in the HITMAN world of assassination trilogy?

Would that be useful?

Who knows?

But we can work it out.

Thanks to the guys at HITMAPS, I have pulled down a JSON format of all the weapons that are in the game.

It is difficult to query a JSON as large as this, so I read it into Microsoft SQL Server with an OPENROWSET command, placed it in a variable and used some JSON query command to write the output to a table.

I had to flatten the json with a cross-apply and write the result set to a table.

Here is a code snippet:

USE MASTER;

GO

DECLARE @json nvarchar(max)

WITH DOCUMENT AS (

SELECT * FROM OPENROWSET(

    BULK N'Path.json',

    SINGLE_CLOB

) AS x

)

,

J AS (

SELECT BulkColumn as colx

FROM DOCUMENT

)

SELECT @json = colx

FROM J

;

WITH A AS (

SELECT [key] as k, [value] as v, [type] as t

FROM OPENJSON(@json) 

)

SELECT *

FROM A CROSS APPLY OPENJSON(v) WITH (

    id INT 'strict $.id',

    Name NVARCHAR(50) '$.name',

    description NVARCHAR(50) '$.description')

       ;

The name of the weapon looks something like this: firearm_hero_ancestral_sniper_name.

With a little bit of string manipulation we can see something more aesthetic.

       SELECT discoverable_weapon, COUNT(discoverable_weapon) as number_of_times_available_to_be_found

       FROM ( SELECT *, REPLACE(REPLACE(REPLACE(REPLACE(NAME,'firearms_hero_',''),'firearm_hero_',''),'_name',''),'_',' ') as discoverable_weapon

       FROM master.dbo.hitman_equipment

       where LEFT(name,7) = 'firearm'

       ) as X

       where 1=1 and

       CHARINDEX('covert',discoverable_weapon,1) > 1

       GROUP BY discoverable_weapon

       ORDER BY number_of_times_available_to_be_found desc