Help with loaded JSON data

[msime]

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

[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.

[Ajeeth11]

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

Thanks.

[msime]

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.

[msime]

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

[james_butler]

Hi

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

Thanks

James

[msime]

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]

Thanks for letting me know.

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