topic Re: Help with loaded JSON data in QlikView

Help with loaded JSON data

msime — Mon, 26 Jan 2026 21:26:37 GMT

Hi,

I'm struggling to find any help with loading JSON data that is returned from an API. It's generating a table for the "column names" array but it seems to assign the same key to each column - is there some documentation that would help me understand this?

Sample data:

{
    "items": [
        {
            "tableName": "incidents",
            "count": 4,
            "columnNames": [
                "id",
                "subject",
                "referenceNumber"
            ],
            "rows": [

Generated Select script:

RestConnectorMasterTable:
SQL SELECT 
	"__KEY_root",
	(SELECT 
		"tableName",
		"count",
		"__KEY_items",
		"__FK_items",
		(SELECT 
			"@Value",
			"__FK_columnNames"
		FROM "columnNames" FK "__FK_columnNames" ArrayValueAlias "@Value"),

Re: Help with loaded JSON data

Gysbert_Wassenaar — Fri, 21 Dec 2018 17:40:04 GMT

The json data is hierarchical. That hierarchy has to be translated to tables at each level of the hierarchy. The __Key* and __FK* fields are used to link those tables to each other so each child table is correctly linked to its parent table.

Re: Help with loaded JSON data

Ajeeth11 — Mon, 31 Dec 2018 07:02:06 GMT

Hi Gysbert,
can you please write some sample code for better understanding of your answer if it possible means?

Thanks.

Re: Help with loaded JSON data

msime — Fri, 04 Jan 2019 14:14:45 GMT

Thanks @Gysbert_Wassenaar I think I'm OK with the idea of parent-child relationships using foreign keys, but can you explain why the primary key is the same for each of the different "columns" in my example screenshot? Or can you point me to some documentation that will help me understand?

Re: Help with loaded JSON data

Gysbert_Wassenaar — Fri, 04 Jan 2019 16:49:14 GMT

The different 'columns' are field values in the same field. All three records are child records of the same parent. So they all get the same key value.

Re: Help with loaded JSON data

msime — Fri, 04 Jan 2019 16:59:59 GMT

Yes - sorry if this is really obvious, but I'm not finding the link between the "columns" and the data in the "rows".

In the example below, how can I tell that the ID is 8? Do I need to modify the select statement to produce the necessary foreign key?

SQL SELECT 
	"__KEY_root",
	(SELECT 
		"tableName",
		"count",
		"__KEY_items",
		"__FK_items",
		(SELECT 
			"@Value",
			"__FK_columnNames"
		FROM "columnNames" FK "__FK_columnNames" ArrayValueAlias "@Value"),
		(SELECT 
			"__KEY_rows",
			"__FK_rows",
			(SELECT 
				"@Value" AS "@Value_u0",
				"__FK_rows_u0"
			FROM "rows" FK "__FK_rows_u0" ArrayValueAlias "@Value_u0")
		FROM "rows" PK "__KEY_rows" FK "__FK_rows")
	FROM "items" PK "__KEY_items" FK "__FK_items"),
	(SELECT 
		"rel",
		"href",
		"mediaType",
		"__FK_links"
	FROM "links" FK "__FK_links")
FROM JSON (wrap on) "root" PK "__KEY_root";

[columnNames]:
LOAD	[@Value],
	[__FK_columnNames] AS [__KEY_items]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_columnNames]);


[rows]:
LOAD	[@Value_u0],
	[__FK_rows_u0] AS [__KEY_rows]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_rows_u0]);

Re: Help with loaded JSON data

james_butler — Mon, 25 Feb 2019 18:14:58 GMT

Did you manage to solve this? I got the same issue and I’m far from a JSON pro.

Thanks

James

Re: Help with loaded JSON data

msime — Wed, 27 Feb 2019 10:32:19 GMT

Unfortunately not. I've got as far as adding a column index to the columnNames and rows tables but I can't see how to join them together and organise the results. Here's how I added indexes:

[columnNames]:
LOAD RowNo( )-1 AS ColumnIndex,
	[@Value],
	[__FK_columnNames] AS [__KEY_items]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_columnNames]);

[rows]:
LOAD Mod(RowNo( )-1,14) AS ColumnIndex,
[@Value_u0],
[__FK_rows_u0] AS [__KEY_rows]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_rows_u0]);

Note that I've got 14 columns in my source data, and I've had to hard-code that number in the script

Re: Help with loaded JSON data

james_butler — Wed, 27 Feb 2019 11:37:06 GMT

Thanks for letting me know.

If I find anything useful I'll let you know.