Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
msime
Contributor II
Contributor II

Help with loaded JSON data

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"),

columnNames.PNG

8 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Ajeeth11
Contributor II
Contributor II

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

Thanks.
msime
Contributor II
Contributor II
Author

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?

 

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
msime
Contributor II
Contributor II
Author

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?
column names2.PNG

 

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]);

 

james_butler
Contributor III
Contributor III

Hi

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

Thanks

James

msime
Contributor II
Contributor II
Author

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 

james_butler
Contributor III
Contributor III

Thanks for letting me know.

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