Parsing a dynamic variable of JSON in stored procedure of SQL Server

Question

I am getting JSON data after consuming an API in my stored procedure. I am trying to parse this JSON but while trying to parse it, I get an error

JSON path is not properly formatted. Unexpected character '@' is found at position 2

I was wondering how can I pass this @variant_description dynamic variable to my parser?

Thank you in advance

ALTER PROCEDURE [dbo].[usp_CallAPI]
    @genome_build nvarchar(50),
    @variant_description nvarchar(50),
    @select_transcripts  nvarchar(50)
AS
BEGIN
    DECLARE @Object AS Int;
    DECLARE @hr int, @APIfunction nvarchar(max)
    DECLARE @json AS TABLE (Json_Table nvarchar(max))
    DECLARE @Json_response AS nvarchar(max) 

    SET @APIfunction = 'https://rest.variantvalidator.org/VariantValidator/variantvalidator/'+ @genome_build+'/'+@variant_description+'/'+@select_transcripts

    EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;

    IF @hr <> 0 
        EXEC sp_OAGetErrorInfo @Object

    EXEC @hr = sp_OAMethod @Object, 'open', NULL, 'get', @APIfunction
                 , --Your Web Service Url (invoked)
                 'false'
    IF @hr <> 0 
        EXEC sp_OAGetErrorInfo @Object

    EXEC @hr = sp_OAMethod @Object, 'send'

    IF @hr <> 0 
        EXEC sp_OAGetErrorInfo @Object

    EXEC @hr = sp_OAMethod @Object, 'responseText', @json OUTPUT

    IF @hr <> 0 
        EXEC sp_OAGetErrorInfo @Object

    INSERT INTO @json (Json_Table) 
        EXEC sp_OAGetProperty @Object, 'responseText'

    -- select the JSON string
    -- SELECT '"elements": ['+Json_Table+ ']' FROM @json
    -- SELECT * FROM @json

    -- Parse the JSON string
    SELECT * 
    FROM OPENJSON((SELECT CONCAT('{', QUOTENAME('elements', '"'), ':['+Json_Table+ ']}') FROM @json), N'$.elements')
    WITH (   
          -- [seqrepo_db] nvarchar(max) N'$.seqrepo_db'   ,
          [variant_description] nvarchar(max) N'$.@variant_description' AS JSON,
          [flag] nvarchar(max) N'$.flag',
          [metadata] nvarchar(max) N'$.metadata' AS JSON
         )

    EXEC sp_OADestroy @Object
END

For example, I want to execute the following

EXEC [dbo].usp_CallAPI @genome_build ='GRCh37',@variant_description = 'NM_000088.3:c.589G>T', @select_transcripts ='all';

For example, this is the test JSON which I am getting back from the API request

{
  "NM_000088.3:c.589G>T": {
    "alt_genomic_loci": [],
    "gene_ids": {
      "ccds_ids": [
        "CCDS11561"
      ],
      "ensembl_gene_id": "ENSG00000108821",
      "entrez_gene_id": "1277",
      "hgnc_id": "HGNC:2197",
      "omim_id": [
        "120150"
      ],
      "ucsc_id": "uc002iqm.4"
    },
    "gene_symbol": "COL1A1",
    "genome_context_intronic_sequence": "",
    "hgvs_lrg_transcript_variant": "LRG_1t1:c.589G>T",
    "hgvs_lrg_variant": "LRG_1:g.8638G>T",
    "hgvs_predicted_protein_consequence": {
      "slr": "NP_000079.2:p.(G197C)",
      "tlr": "NP_000079.2(LRG_1p1):p.(Gly197Cys)"
    },
    "hgvs_refseqgene_variant": "NG_007400.1:g.8638G>T",
    "hgvs_transcript_variant": "NM_000088.3:c.589G>T",
    "primary_assembly_loci": {
      "grch37": {
        "hgvs_genomic_description": "NC_000017.10:g.48275363C>A",
        "vcf": {
          "alt": "A",
          "chr": "17",
          "pos": "48275363",
          "ref": "C"
        }
      },
      "grch38": {
        "hgvs_genomic_description": "NC_000017.11:g.50198002C>A",
        "vcf": {
          "alt": "A",
          "chr": "17",
          "pos": "50198002",
          "ref": "C"
        }
      },
      "hg19": {
        "hgvs_genomic_description": "NC_000017.10:g.48275363C>A",
        "vcf": {
          "alt": "A",
          "chr": "chr17",
          "pos": "48275363",
          "ref": "C"
        }
      },
      "hg38": {
        "hgvs_genomic_description": "NC_000017.11:g.50198002C>A",
        "vcf": {
          "alt": "A",
          "chr": "chr17",
          "pos": "50198002",
          "ref": "C"
        }
      }
    },
    "reference_sequence_records": {
      "lrg": "http://ftp.ebi.ac.uk/pub/databases/lrgex/LRG_1.xml",
      "protein": "https://www.ncbi.nlm.nih.gov/nuccore/NP_000079.2",
      "refseqgene": "https://www.ncbi.nlm.nih.gov/nuccore/NG_007400.1",
      "transcript": "https://www.ncbi.nlm.nih.gov/nuccore/NM_000088.3"
    },
    "refseqgene_context_intronic_sequence": "",
    "submitted_variant": "NM_000088.3:c.589G>T",
    "transcript_description": "Homo sapiens collagen type I alpha 1 chain (COL1A1), mRNA",
    "validation_warnings": []
  },
  "flag": "gene_variant",
  "metadata": {
    "seqrepo_db": "2018-08-21",
    "uta_schema": "uta_20180821",
    "variantvalidator_hgvs_version": "1.2.5.vv1",
    "variantvalidator_version": "1.0.4.dev17+gd16b9ef.d20200422"
  }
}

nvarchar(50) seems a bit short for the @variant_description parameter - maybe you're just cutting off part of the JSON? I'd make it a nvarchar(max) parameter instead — marc_s
– marc_s, Commented Sep 30, 2020 at 11:49
Simply use '$."@variant_description"' as path expression in the OPENJSON() call. From docs - If the key name starts with a dollar sign or contains special characters such as spaces, surround it with quotes. — Zhorov
– Zhorov, Commented Sep 30, 2020 at 11:57
@AshkanPirmani What is the actual value stored in the Json_Table column? If possible, post test data. Thanks. — Zhorov
– Zhorov, Commented Sep 30, 2020 at 12:07

Zhorov · Accepted Answer · 2020-09-30 13:48:16Z

1

I don't think that you can use a variable for a path expression, when you use OPENJSON() and explicit schema (the WITH clause). But, if the JSON response has a fixed structure - a JSON object (not array) with three key\value pairs (first with a variable name, "flag" and "metadata"), you may try to change the way you are parsing the JSON and use JSON_VALUE() and JSON_QUERY(). In this case, starting from SQL Server 2017, you can provide a variable as the value of path. Also, you do not need to build a new JSON object ({"elements:[...]"}), simply parse the returned JSON response.

Use the following statement at the end of your stored procedure:

SELECT 
   variant_description = JSON_QUERY(@json, '$."' + @variant_description + '"'),
   flag = JSON_VALUE(@json, '$."flag"'),
   metadata = JSON_QUERY(@json, '$."metadata"')

instead of:

SELECT * 
FROM OPENJSON(
   (SELECT CONCAT('{', QUOTENAME('elements', '"'), ':['+Json_Table+ ']}') FROM @json), 
   N'$.elements'
) WITH (   
   -- [seqrepo_db] nvarchar(max) N'$.seqrepo_db'   ,
   [variant_description] nvarchar(max) N'$.@variant_description' AS JSON,
   [flag] nvarchar(max) N'$.flag',
   [metadata] nvarchar(max) N'$.metadata' AS JSON
)

As a side note (and for your future work), the actual reason for the "JSON path is not properly formatted. Unexpected character '@' is found at position 2" error is the fact, that you use N'$.@variant_description' as a path expression. As is explained in the documentation, if ... the key name starts with a dollar sign or contains special characters such as spaces, surround it with quotes (e.g. N'$."@variant_description"').

edited Sep 30, 2020 at 13:48

answered Sep 30, 2020 at 12:39

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ashkan Pirmani Over a year ago

Thank You, That work very well, @json was not defined but with simple (select * from @json) it works fine!

Collectives™ on Stack Overflow

Parsing a dynamic variable of JSON in stored procedure of SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related