Querying JSON that looks like an array

[mrjcleaver]

Hi,

I'm trying to built a query that will match against an array value. I think I have my quoting wrong.

I generate alasql_including_query.js to become:

//var alasql = require('/usr/local/lib/node_modules/alasql/alasql.min.js');
if(typeof exports === 'object') {
        var assert = require("assert");
        var alasql = require('/usr/local/lib/node_modules/alasql/alasql.min.js');   // technically a straight require("alasql") should work
} else {
        __dirname = '.';
};

alasql.fn.myDuration = function(secs) {
    return Math.floor(secs/60);
}

function stringify_alasql(query) {
        alasql(query,[],function(res){
                  console.log(JSON.stringify(res));
        })
}
var result ="SELECT projects, path, application, myDuration([duration]) as minutes             FROM json(\"\")             WHERE startDate = \"2015-10-07, 10:00 AM\"             AND projects = \"[\\\"4. Client Work/4. Client: XX [MCAB-9]\\\"]\"             GROUP BY projects, minutes, path, application, duration             ;"
stringify_alasql(result)

Where timing_output looks like:

[
  {
    "path" : "https:\/\/...\/display\/ATL\/Architecture+Approach+1",
    "projects" : [
      "4. Client Work\/4. Client: XX [MCAB-9]"
    ],
    "endDate" : "2015-10-07, 10:00 AM",
    "startDate" : "2015-10-07, 9:00 AM",
    "application" : "Safari",
    "duration" : 89.99672394990921
  },
  {
    "path" : "https:\/\/...\/display\/ATL\/Atlassian+Enterprise+Architecture",
    "projects" : [
      "4. Client Work\/4. Client: XX [MCAB-9]"
    ],
    "endDate" : "2015-10-07, 10:00 AM",
    "startDate" : "2015-10-07, 9:00 AM",
    "application" : "Safari",
    "duration" : 67.80467706918716
  },
  {
    "path" : "https:\/\/...\/pages\/editpage.action\/?pageId=41625335",
    "projects" : [
      "4. Client Work\/4. Client: XX [MCAB-9]"
    ],
    "endDate" : "2015-10-07, 10:00 AM",
    "startDate" : "2015-10-07, 9:00 AM",
    "application" : "Safari",
    "duration" : 81.00990098714828
  },
  {
    "path" : "\/Volumes\/Storage\/martincleaver\/SoftwareDevelopment\/newchoir_sets\/playlist_builder.rb",
    "projects" : [
      "0. Ambiguous\/X. Ambiguous: Software Development",
      "D. Timesuck: Newchoir"
    ],
    "endDate" : "2015-10-07, 9:00 PM",
    "startDate" : "2015-10-07, 8:00 PM",
    "application" : "RubyMine",
    "duration" : 269.991591989994
  },
  {
    "path" : "http:\/\/...\/display\/AR\/2015\/10\/07\/Playlist+Builder+run+for+2015-10-07-20-43-24",
    "projects" : [
      "D. Timesuck: Newchoir"
    ],
    "endDate" : "2015-10-07, 9:00 PM",
    "startDate" : "2015-10-07, 8:00 PM",
    "application" : "Safari",
    "duration" : 60.54301196336746
  }
]

When I run:

$ cat "/tmp/timing_output" | node '/tmp/alasql_including_query.js' 

I get: [{"minutes":null}]

If I omit my where clause I do get results, so I can only conclude that I am quoting the WHERE clause incorrectly.

I would appreciate any help!

[agershun]

I tried to extract the query from the strings:

var result ="SELECT projects, path, application, myDuration([duration]) as minutes   \
     FROM json(\"\")             \
    WHERE startDate = \"2015-10-07, 10:00 AM\"             \
    AND projects = \"[\\\"4. Client Work/4. Client: XX [MCAB-9]\\\"]\"             \
   GROUP BY projects, minutes, path, application, duration             ;"

Probably the problem is in this part of the query:

   projects = \"[\\\"4. Client Work/4. Client: XX [MCAB-9]\\\"]\"

Please try to use this construction:

    projects->0 = \"4. Client Work/4. Client: XX [MCAB-9]\\\"

Here projects is a variable with array inside, so AlaSQL does not allow to use constructions like this:

    ... WHERE myarray = @[1,2,3]

In this case you can use:

    ... WHERE myarray->0 = 1 AND myarray->1 = 2 AND myarray->2 = 3

or use deep equal operator ===:

    ... WHERE myarray === @[1,2,3]

BTW: in AlaSQL we have to use @[ ] for arrays instead of [ ] to do not mix with traditional SQL notation for column names.

[mrjcleaver]

Hi,

Ok, indeed, I meant to say I knew it was the projects part of the WHERE clause is the issue.

I tried your example:

    projects->0 = \"4. Client Work/4. Client: XX [MCAB-9]\\\"

1:

AND projects->0 = \"4. Client Work/4. Client: XX [MCAB-9]\\\"   

I get:

undefined:2
artDate']==='2015-10-07, 10:00 AM')&&(p['default']['projects'][0]==='4. Client
                                                                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
SyntaxError: Unexpected token ILLEGAL
    at Object.Function (<anonymous>)
    at yy.Select.compileWhere (/usr/local/lib/node_modules/alasql/alasql.min.js:4:28718)

Assuming you didn't test, I changed to:

AND projects->0 = \"4. Client Work/4. Client: XX [MCAB-9]\"

But that results in:

[{"minutes":null}]

Intricacies include: 1) that my content also includes the [MCAB-9] reference - this is not an array but rather just content.

e.g.

    "projects" : [
      "4. Client Work\/4. Client: XX [MCAB-9]"
    ],

2) the value I am matching is actually the result of a previous query:

    projects = ...
                select projects from JSON("") WHERE startDate = "#{startDateAndTimeBegin}"
                GROUP BY projects
                ;

So projects is an array of projects, not a singleton. e.g.

    "projects" : [
      "0. Ambiguous\/X. Ambiguous: Software Development",
      "D. Timesuck: Newchoir"
    ],

And I have no control in which order the results will come, so I can't positionally assert what's in ->0 vs ->1.

Because I want to test against the literal set of projects I found before I thought this looked promising:

projects === \"@[\\\"4. Client Work/4. Client: TD [MCAB-9]\\\"]\"                    ;"

But it yielded no results. Is that a literal match? Does it ignore the [MCAB-9]? I did try escaping, thusly:

AND projects === \"@[\\\"4. Client Work/4. Client: TD \[MCAB-9\]\\\"]\" 

That didn't work either!

I suppose this is an escaping issue - that I need to feed my projects string into something to allow alasql to treat the whole thing as content.

Thanks again.

[agershun]

Martin, may I ask you to create a simple example in jsFiddle.com?

[mrjcleaver]

Ok - this actually doesn't quite work either, but it's a start: http://jsfiddle.net/foehu5qp/12/

Thanks for your help

[mrjcleaver]

Oh, I meant to mention: the jsFiddle errors out (see the browser's JS console), so what it shows on the results panel is an intermediate step result.

The error is: "Error: Parse error on line 1: SELECT projects, path, application, m -----------------^ Expecting 'LITERAL', 'BRALITERAL', 'LPAR', 'NUMBER', ..."

[mrjcleaver]

Ok - so this error looks like the sort of thing shown on https://github.com/agershun/alasql/issues/87 - I think it's peculiar to use in jsFiddle, not to do with my test case. @mathiasrw - could you care to comment? I'm stuck here and I think @agershun is has a lot on his plate.

Thanks kindly, Martin.

[mathiasrw]

@mrjcleaver I did not go into this as it looked like agershun got it covered.

I see that you have the following in your sql at jsfiddle: AND projects = \"[\\\"4. Client Work/4. Client: XX [MCAB-9]\\\"]\"

You must include the value of the string - not the js syntax - try with something like AND projects = '4. Client Work/4. Client: XX [MCAB-9]'

[mathiasrw]

@mrjcleaver have you had time to look into if this solves the problem?

[mrjcleaver]

Thanks @mathiasrw

My projects setting is actually an array - I tried updating the jsFiddle per your recommendation

However, it just crashes. Frankly I'm not sure whether this is a jsFiddle issue or an issue with the SQL.

Sorry to ask but I'm really stuck - could you fork it?

Thanks again.

[stevemccann]

I've managed to get the following select statement working on my computer:

var result =
        'SELECT projects, "path", application, myDuration([duration]) ' 
        + 'AS minutes '
        + 'FROM json("timing_output") '
        + 'WHERE startDate = "2015-10-07, 9:00 AM" ' 
        + ' AND projects->0 = "4. Client Work\/4. Client: XX [MCAB-9]" '
        + ' GROUP BY projects, minutes, "path", application, duration  ;';

errors I've found so far:

• startDate of 10:00 AM was not in source dataset, changed to 9:00 AM
• path field name seems to throw an error. Not sure if it's a reserved keyword? Surrounded with quotes seems to fix

I also changed the following:

• changed double quotes to single, to allow inline use of double-quotes

[mrjcleaver]

Thanks Steve.

Ok - this (I've put your changes into http://jsfiddle.net/mrjcleaver/foehu5qp/) is much better. It returns one result and doesn't crash! (V. good idea on the quotes)

However, at least on jsFiddle, I simply see:

[
    {
        "'path'": "path"
    }
]

My laptop is out for repair - do you get the same result on the command line? I can't change the attribute name.

Lastly, I won't know at which array position (Steve's testing [0]) the value I am looking for will occur. Is is possible to match them all?

Thanks, Martin.

[mathiasrw]

@stevemccann thanks for stepping in

@mrjcleaver

Replace the output = alasql(select, input_json) with output = alasql(select, [input_json])

It gives you some results, but cant figure out if they are correct.

When using ? its important to note that the second argument to alasql is an array where first ? will be replaced with the first element, the second ? with the second element and so forth. Not having your table structure in an array basicly ment that you where making an sql on the first element on your table...

When in doubt go back to SELECT * FROM ? WHERE 1 to verify data.

[mrjcleaver]

Ok - so I've updated to [input_json] - much better.

But - I'm confused - what's happening with path?

var select ='SELECT path FROM ?   WHERE 1   ;'

Crashes it. And using "path" gives me the literal.

[
    {
        "'path'": "path"
    },
    {
        "'path'": "path"
    },
    {
        "'path'": "path"
    },
    {
        "'path'": "path"
    },
    {
        "'path'": "path"
    }
]

[mathiasrw]

Its a keyword

https://github.com/agershun/alasql/wiki/PATH

please use [path] instead of path

[mrjcleaver]

Perfect. So that's the path thing sorted, thanks.

One left - how do I match a value in an array? Steve's answer assumes ->0 but I've updated the first data element in jsFiddle with an example of where what we are looking for appears in a later position (->1).

I also looked at https://github.com/agershun/alasql/wiki/Search, the WHERE and SELECT - but neither of those are apparently applicable.

Thanks, M.

[mrjcleaver]

It would also be helpful if a hint was shown when alasql detected a term that is a keyword (e.g. path) in a position that would otherwise be a column name.

[mrjcleaver]

Maybe https://github.com/agershun/alasql/wiki/How%20to%20search%20deep%20nested%20JSON and https://github.com/agershun/alasql/wiki/Search ... it's a bit dizzying!

[mrjcleaver]

e.g. http://jsfiddle.net/mrjcleaver/foehu5qp/28/ only detects in the ->1 position.

[mrjcleaver]

Unless I'm mistaken, the answer is not in: https://github.com/agershun/alasql/wiki/WHERE https://github.com/agershun/alasql/wiki/In or https://github.com/agershun/alasql/wiki/Array :(

[mathiasrw]

@mrjcleaver I agree. Maybe @agershun can help?

[mrjcleaver]

Which part do you agree with @mathiasrw? All points? Thx.

[mathiasrw]

It would also be helpful if a hint was shown when alasql detected a term that is a keyword (e.g. path) in a position that would otherwise be a column name.

I agree

http://jsfiddle.net/mrjcleaver/foehu5qp/28/ only detects in the ->1 position

I agree

the answer is not in: https://github.com/agershun/alasql/wiki/WHERE https://github.com/agershun/alasql/wiki/In or https://github.com/agershun/alasql/wiki/Array

I agree

np

[mrjcleaver]

@agershun appears to be away. I don't suppose you know when he'll be back, do you?

[mathiasrw]

Nope... never met him...

[mathiasrw]

Did you solve this?

[mathiasrw]

Im closing this issue based on inactivity. Please reopen if any progress the case.