AlaSQL can read and store data in JSON format.

Property operator ->

INSERT INTO oneVALUES @{a:5, b:{c:@[4,5]}}SELECT*FROM oneWHERE a->b->0=4

Please note that negavie numbers must be enclosed like this:(-5). There isan issue to get it fixed.

Expression

SELECT*FROM oneWHERE a->(LCASE(“B”))->(1-1)=4

Arrow function ->

object->function(parameters)

Select lengths of all lines from text file

alasql(‘SELECT[0]->lengthFROMTXT(“mytext.txt”)’alasql(‘SELECTLEN([0])FROMTXT(“mytext.txt”)’

Arrow function -> property

vardata=[{a:{b:1,c:1},{a:{b:2}}}]alasql(‘SELECTa->bFROM ?’,[data]);

Array members

SELECT a->(0)FROM data

Calculated property names

SELECT a->(“mon”+moid), b->(2+2)FROM data

Object property

• a -> b
• a -> b -> c

Array member

• a -> 1
• a -> 1 -> 2

Calculated property name

• a -> (1+2)
• a -> ("text2 + " " + "more")

Functions

• myTime -> getFullYear()
• s -> substr(1,2)

JavaScript string functions can also be used

SELECT s->lengthFROM mytext

@ prefixes (like Objective-C NSObjects)

• @1
• @”string”
• @{a:1,b:2} or {a:1,b:2}
• @[1,2,3] – conflct with column names with spaces [My Column]

Three equal operators

• a = b like == in JavaScript
• a == b compare a.valueOf() and b.valueOf() – for dates
• a === b uses equalDeep() – for JSON objects

    INSERT INTO one VALUES @{b:1}, @{b:2}    SELECT @{a:@[2014,(2014+1),(2014+b)]} FROM one    [{a:[2014,2015,2015]}, {a:[2014,2015,2016]}]

JSON table

CREATETABLEone;INSERT INTO oneVALUES @{a:1}, @{b:2}, @{a:1,b:2}, @1, @”String”

JSON object

CREATETABLEtwo (a JSON);INSERT INTO oneVALUES (1), (‘two’), (@{b:’three’}), @[‘F’,’O’,’U’,’R’]

SELECT*FROM one    [{a:1}, {b:2}, {a:1,b:2},1, ”String”]SELECT aFROM one    [{a:1}, {a:undefined}, {a:1}, {a:undefined},{a:undefined}]SELECT*FROM oneWHERE a=1    [{a:1},{a:1,b:2}]

Please note that you can avoid letting AlaSQL try to add extension to filenames by settingautoExt:false in the options given.

See also

• [How to search deep nested JSON?](How to search deep nested JSON)
• [How to traverse a complex JSON doc with javascript and extract named values?](How to traverse a complex JSON doc with javascript and extract named values)
• [How to find all parents elements in a JSON file?](How to find all parents elements in a JSON file)
• [How to recursive find and replace in multidimensional JavaScript object?](How to recursive find and replace in multidimensional JavaScript object)