Extracting data from a json formatted field in a SQL server database

[aschmeelk]

Boy do I need help! As of May 2025 Qlik Sense Enterprise on Windows supports Native json as per the following: 

Direct JSON file loading: Supports loading .json files directly into Qlik Sense without additional connectors, and interrogation of JSON within a single field, for example out of a database.

I have seen many examples of working with .json files, but have had no luck finding any examples of working with the 'interrogation of JSON within a single field' from a data base.

So, here's what I have:

LOAD *; SELECT

id as specification_id,

name as cam_code,

type as cam_type,

cam_details,

dealer_id as DEALER_SETUP_KEY

FROM cam_specification;

Where 'cam_details' is text formatted as json:

[

  {

    "id": "cfb1164a-a2a8-4e19-933f-a00bc74d5e09",

    "workType": "RR",

    "label": null,

    "makes": ["All"],

    "series": ["All"],

    "sizes": ["44550R225"],

    "applicationDefinition": "LINEHAUL",

    "axles": ["All"],

    "treads": ["PXONEXTE"],

    "ntr": {

      "limit": 9,

      "action": "REJECT",

      "disposition": "RETURN",

      "disposalCode": null,

      "application": null,

      "axle": null,

      "treads": null

    },

    "age": {

      "limit": 84,

      "action": "REJECT",

      "disposition": "RETURN",

      "disposalCode": null,

      "application": null,

      "axle": null,

      "treads": null

    },

    "repair": {

      "limit": null,

      "action": "REJECT",

      "disposition": "RETURN",

      "disposalCode": null,

      "application": null,

      "axle": null,

      "treads": null

    },

    "limits": [

      {

        "sectionNumber": 2,

        "totalRepairs": 5,

        "totalNailRepairs": 5,

        "totalRnailCrownRepairs": 5,

        "totalRnailShoulder": 0,

        "totalRnailShoulderSize": "SR26",

        "totalSectionRepairs": 0,

        "totalCrownSection": 0,

        "totalCrownSectionSize": null,

        "totalShoulderSection": 0,

        "totalShoulderSectionSize": null,

        "totalSidewallSection": 0,

        "totalSidewallSectionSize": null,

        "spot": 2,

        "bead": 2,

        "liner": 2,

        "brake": 0

      },

      {

        "sectionNumber": 1,

        "totalRepairs": 4,

        "totalNailRepairs": 4,

        "totalRnailCrownRepairs": 1,

        "totalRnailShoulder": 1,

        "totalRnailShoulderSize": "SR26",

        "totalSectionRepairs": 1,

        "totalCrownSection": 1,

        "totalCrownSectionSize": "SR40",

        "totalShoulderSection": 1,

        "totalShoulderSectionSize": "SR40",

        "totalSidewallSection": 0,

        "totalSidewallSectionSize": null,

        "spot": 2,

        "bead": 2,

        "liner": 2,

        "brake": 0

      }

    ],

    "createdDate": [2024, 12, 16, 9, 21, 42, 871157621],

    "modifiedDate": [2024, 12, 16, 9, 21, 42, 871157621],

    "logicalDelete": "N",

    "defaultProductSpec": false,

    "exceedMichelinSpec": true

  },

  {...

]

 This json may contain a variable number of json objects, each representing a 'table' if you will. The kicker is that none of the 'id' fields within the json link to the id of the sql table row. So pulling them out using From_Field and the 'All Tables' qualifier produces a number of tables that are no longer linked to the primary 'cam_specification' table.

I have tried using Preceeding Loads and the jsonget() function to parse through the field, but it requires a separate function call for each object and array item in the field, which is roughly 12,000 characters long on average! Attaching the full json for one row of cam_details below.

Please, if anyone has any experience with this let me know what you've found, I'm at my wits end  with this project.

[Dataintellinalytics]

HI,

Can you explain more on this "The kicker is that none of the 'id' fields within the json link to the id of the sql table row."

PFA, I tried to replicate and got the attached output.
Note: Used only two id in json because of excel cell limitation.

[aschmeelk]

Thanks for your response! Regarding your question: Can you explain more on this "The kicker is that none of the 'id' fields within the json link to the id of the sql table row." I mean none of the 'id' values in the json relate to the primary id of the row that the json data belongs to. Here's the schema for the table this comes from: 

So there is no 'id' within the json which relates back to the primary key of the table. What I want is to expand the json and append it to the existing table so that the json data will maintain the relationship to its row.