Another lookup table question :)

[jothoma1 (Johan THOMAS)]

Hi,
i’m testing this: https://github.com/o1lab/xmysql
It provides a REST API for mysql, i have a lot of use case with lookup tables but i can’t make it work…
My guess is that lookup tables dont like the output with [ ]
Example of an output:

[{"id":235942,"auth":"cas","confirmed":1,"policyagreed":0,"deleted":1,"suspended":0,"mnethostid":3,"username":".1410254287","password":"not cached","idnumber":"","firstname":"Emile","lastname":"Dubre","email":"8529a12c9c42ea6fa7b5a762deada5f6","maildisplay":2,"autosubscribe":1,"trackforums":0,"timecreated":1419036228,"timemodified":1410254288,"trustbitmask":0,"imagealt":null,"lastnamephonetic":null,"firstnamephonetic":null,"middlename":null,"alternatename":null,"calendartype":"gregorian"}]

I just want to use it for simple id & username resolution.
Have you an idea ? Is it because of “[]” ?

[jochen]

Please provide some details about what you’re trying to achieve and what’s not working.

The configuration of your lookup table(s) would be a start.

[jothoma1 (Johan THOMAS)]

Hi @jochen
Yes of course, i’m trying to use a HTTP JSONPath data adapter. And i’m just trying the lookup with the test feature.
My xmysql Rest API give me something like this (it works well) :

[{“id”:235942,“auth”:“cas”,“confirmed”:1,“policyagreed”:0,“deleted”:1,“suspended”:0,“mnethostid”:3,“username”:“.1410254287”,“password”:“not cached”,“idnumber”:“”,“firstname”:“Emile”,“lastname”:“Dubre”,“email”:“8529a12c9c42ea6fa7b5a762deada5f6”,“maildisplay”:2,“autosubscribe”:1,“trackforums”:0,“timecreated”:1419036228,“timemodified”:1410254288,“trustbitmask”:0,“imagealt”:null,“lastnamephonetic”:null,“firstnamephonetic”:null,“middlename”:null,“alternatename”:null,“calendartype”:“gregorian”}]

And i dont know how to configure in the data adapter the Multi value JSONPath and Single value JSONPath ? everything i try did not work.
And i assumed that it is because of the “” that my REST API ?

[jochen]

@jothoma1

And i dont know how to configure in the data adapter the Multi value JSONPath and Single value JSONPath ?

What do you expect the lookup to return?

[jothoma1 (Johan THOMAS)]

@jochen thanks for your response.
Any of the fields present, like for exemple username

[jothoma1 (Johan THOMAS)]

Hi @jochen
Do you have an idea ?

[kay (Kay Roepke)]

Hi!

I’ve just configured a data adapter with the JSON output you provided (simply put it into a static file, including the array) and it works as expected:

The key is to unwrap the array using JSONPath. My config assumes that there’s only a single element in the list.
If you use the multi value in pipeline rules, you should also be able to simply index the result value with [0] or so, For extractors/converters you will need to unwrap the array into a map, though.

Hope that helps!