JSONFilter Question

Topics: Example / Documentation
Feb 1 at 2:31 PM
I am attempting to transfer JSON output from a smart home controller into a SQL Server database. Creating columns from the top level JSON object output works fine.

I have yet to successfully filter a 'sub' collection of values into its own table, since the JSONfilter errors out. The answer would well be described in the post 'Deserialization of Json data' but the images are not not visible.

Screenshot of SSIS package with error and JSONFilter settings :
https://i.imgsafe.org/1fe0031e96.png

URL request returns details from a single device (ref is the 'key') :
http://internalHostname/JSON?request=getstatus&ref=21

JSON output

{
"Name": "HomeSeer Devices",
"Version": "1.0",
"Devices": [{
    "ref": 21,
    "name": " Front Door Notification Sensor",
    "location": "Lounge",
    "location2": "Ground",
    "value": 0,
    "status": "No Status",
    "device_type_string": "Z-Wave Notification Sensor",
    "last_change": "\/Date(-59011459200000)\/",
    "relationship": 2,
    "hide_from_view": true,
    "associated_devices": [22,
    23,
    24,
    25],
    "device_type": {
        "Device_API": 4,
        "Device_API_Description": "Plug-In API",
        "Device_Type": 999,
        "Device_Type_Description": "Plug-In Root Device",
        "Device_SubType": 7,
        "Device_SubType_Description": ""
    },
    "device_image": "",
    "UserNote": "",
    "UserAccess": "Any",
    "status_image": "/images/HomeSeer/status/nostatus.gif"
}]
}

Any ideas ? is the filter incorrectly setup ?

Thanks

Dom
Coordinator
Feb 3 at 10:48 AM
Edited Feb 3 at 10:48 AM
Hi Dom,

EDIT: I've uploaded a new version of the component which may solve some other issue. So, before trying this out, make sure you download it and install it first!

I've taken a look at your data and tried to make it work. However, your case is quite different from the problem this component solves. Let me explain why.
In most cases, you want to convert a json object into a table. This basically means that the paradigm "key" -> "value" of a json object has to be translated to "column"->"value". That should be quite clear, right? So, when you specify an array root path, the component will look of each JSON OBJECT, and for each one, will lookup every key->value, copying those into columns->value pairs. Did I lose you, I guess not?

Now, follow me. All the elements in your input data follow this rule, except the data specified into the associated_devices. In fact, composed data is an array of VALUES, not an array of objects. So, when you try the classic approach, the component is not able to find any object, because the array you specify is composed by values, without keys. That said, how does the component know where to put such values? Obviously, such a problem might be solved by implementing some logic that "explodes" lines, as you would imagine. However that approach would make the component even more complex, and I don't like that. On the contrary, other possibilities are behind the corner.

My idea is to implement other sub-component types, which work with UNIT of data. In you case, you need a component that takes an array of values N as input and outputs a table with a single column and N rows. Does it make sense?

Anyways, such implementation will require time, and won't happen quickly. Thus, you might not want to wait for it.

However, there are two possible workardounds that I can think of. One of them, very UGLY, would be to manually parse each possible index of associated devices. This is only applicable when the maximum number of associations is somehow known. In that case, you can get values into columns, one column for each associated device. I repeat, that is not the way to go, but is a possible workaround until the new component comes out. Here there are screens that explain to you how to do this:
Image

Image

Image

Image

Then, you can use PIVOT operation to get those in column instead of rows.
Is that clear?

Now with the second possible approach, get your hands dirty with some code, by using a script component.
Configure stuff as here:
Image

Image

Image

Then open the code editor for the script component and edit the Input0_ProcessInputRow method, as follows:
public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // This code is invoked for each input row. Thus we want to copy the ref and split the associated devices.
        var without_brackets = Row.ASSOCIATEDDEVS.Trim(new char[] { '[', ']' });
        var tokens = without_brackets.Split(',');

        foreach (var token in tokens) {
            Output0Buffer.AddRow();
            Output0Buffer.REF = (int)Row.REF;
            Output0Buffer.ASSOCIATEDDEVICE = int.Parse(token);
        }
    }
Close it and run it. You should get the following:

Image


I take this chance to apologize for the missing images from the documentation. I definetely need to take care of it. However, as I continue discovering errors and better ways to handle those (as this missing piece of converting raw json values into tables), I realize there are more urgent stuff to do. I am also approaching my graduation date and this makes things much harder for me :S

I hope this will help you in a way... and please let me known if there is something you didn't understand.

Cheers,
A.
Marked as answer by webking on 2/21/2017 at 1:40 AM
Feb 4 at 8:49 AM
Hi Alberto,

Thankyou for a detailed reply.

I understand fully, the script solution is perfectly acceptable and works fine.

Dom