FLAT(

[mathiasrw]

Spawned from #409

alasql.stfn.FLAT = function(obj,opts) { 

    opts = opts || {}
    var levels = opts.levels | 0 || 100; 
    var objDevider = opts.objDevider || '.';
    var arrayDevider = opts.arrayDevider || [];
    var showArrayAsObjec = !!opts.showArrayAsObjec;
    var arrayDeviderPre = arrayDevider[0] || '[';
    var arrayDeviderPost = arrayDevider[1] || ']';

    function flatten(obj, path, result, level, parrentIsObj) {

            if(level > levels){
                return;
            }

            for (var prop in obj) {
                    if (obj.hasOwnProperty(prop)) {
                            var name
                            if(0===level){
                                name = prop
                            } else if (parrentIsObj || showArrayAsObjec){
                                name = path + objDevider + prop
                            }else{
                                name = path + arrayDeviderPre + prop + arrayDeviderPost
                            }

                            if (typeof obj[prop] == "object"){
                                flatten(obj[prop], name, result, level+1, obj[prop].constructor !== Array);
                            }else{
                                result[name] = obj[prop]
                            }
                    }
        }
        return result
    }

return flatten(obj, '', {}, 0);
}

alasql.from.FLAT = function(obj, opts, cb, idx, query) {
    var res = alasql.stdfn.FLAT(obj,opts);
    if(cb){
        res = cb(res, idx, query);
    }
    return res;
 };

[mathiasrw]

From #154

Will I be able to

var data = [{a:1}];
var res = alasql('FLAT OF SELECT _ AS q, _->a AS w FROM ?',[data]);
// [{q.a:1, w:1}]

?

[agershun]

We need a little bit another function:

flat({}) -> [{}]
flat({a:1}) -> [{a:1}]
flat({a:{b:1}}) -> [{'a.b:1}]
flat([]) -> []
flat([1,2]) -> [1,2]
flat([{a:1}] -> [{a:1}]
flat([{a:[1,2]}]) -> [{a:1},{a:2}]
flat([{a:[1,2],b:5}]) -> [{a:1,b:5},{a:2,b:5}]
flat([[1,2]]) -> [1,2]
flat([{a:{b:[{e:1},{f:1}]}}]) -> [{'a.b.e':1},{'a.b.f':1}]

May be we need two different versions (or options):

flat([{f:1, a:{b:[{e:1},{f:1}]}}]) -> [{f:1, 'a.b.e':1},{f:1, 'a.b.f':1}]  // option 1
flat([{f:1, a:{b:[{e:1},{f:1}]}}]) -> [{f:1},{f:1, 'a.b.e':1},{f:1, 'a.b.f':1}]  // option 2 with ROLLUP

[agershun]

Another example:

{
    'name': 'a',
    'kid': {
        'level': 2,
        'sun': [5454,{
        'name': 'b',
        'level': 2,
        'sun': [{
            'name': 'c',
            'level': 3
        },{
            'name': 'c',
            'level': 3
        }]
     }]}
   }

returns

   [
    { 'name': 'a','kid.level':2, 'kid.sun:5454},
    {'name': 'a','kid.level':2, 'kid.sun.level': 2, 'kid.sun.level': 2, 'kid.sun.sun.name':'c', 'kid.sun.sun.level':3},  
    {'name': 'a','kid.level':2, 'kid.sun.level': 2, 'kid.sun.level': 2, 'kid.sun.sun.name':'c', 'kid.sun.sun.level':3},  
   ]

[mathiasrw]

Some of your examples I dont understand. If you could elaborate on the logic behind that would be awesome.

First: you want it to be an array always. I fixed that.

• flat({}) -> [{}] is ok
• flat({a:1}) -> [{a:1}] is ok
• flat({a:{b:1}}) -> [{a.b:1}] is ok
• flat([]) -> [] is ok

I understand from this that if there is only one level you want the same data (nothing to flatten)

• flat([1,2]) -> [1,2]
• flat([{a:1}] -> [{a:1}] why is it better to skip the information about the array? to me it makes sense to get a [{'0.a':1}].
• flat([{a:[1,2]}]) -> [{a:1},{a:2}] what is the idea here? Can you give a more complex example of how you see it should work?
• flat([{a:[1,2],b:5}]) -> [{a:1,b:5},{a:2,b:5}] what is the idea here? Can you give a more complex example of how you see it should work?
• flat([[1,2]]) -> [1,2] Why?
• flat([{a:{b:[{e:1},{f:1}]}}]) -> [{'a.b.e':1},{'a.b.f':1}] what is the idea here? Can you give a more complex example of how you see it should work?

http://jsfiddle.net/40oq02g4/3/

---

flat([{f:1, a:{b:[{e:1},{f:1}]}}]) -> [{f:1, 'a.b.e':1},{f:1, 'a.b.f':1}] // option 1 `flat([{f:1, a:{b:[{e:1},{f:1}]}}]) -> [{f:1},{f:1, 'a.b.e':1},{f:1, 'a.b.f':1}] // option 2 with ROLLUP``

I dont understand your logic regarding why the f:1 is duplicated.

[agershun]

Let's start from the beginning: why does AlaSQL need this function:

I think we can start with this SO problem

AlaSQL could solve this problem with next (hypotetical) simple statement:

alasq('SELECT * INTO XLSX("myfile.xlsx",{headers:true}) FROM FLAT(?)',[data]);

So, FLAT() function should expand the object from the example to the array of simple one-level objects.

This is not the only one quersion. For example, see here:

• http://stackoverflow.com/questions/34472528/getting-all-array-property-data-using-alasql/34496203#34496203

[mathiasrw]

Good idea to start from the start.

A generic solution for fitting data like this SO problem into excel is not obvius. I cant see how it makes sense without giving som parameters on what you want to itterate over (it would make sense to have an entry per "resources" - I agree)

---

Lets take this example

var data = [
{
   a: 'test',
   b: [
       {c:'test1',
        d: 'test2'},
       {c:'test2', d: 'test1'}]
  },
 {
   a: 'testB',
   b: [
       {c:'test3',
        d: 'test4'},
       {c:'test5', d: [8,9]}]
  }
];

what should the FLAT( of that one be? (sorry - im really not getting it)

[agershun]

array of 5 rows;

[
  {a:'test', 'b.c':'test1', 'b.d':'test2'},
  {a:'test', 'b.c':'test2', 'b.d':'test1'},
  {a:'testB', 'b.c':'test3', 'b.d':'test4'},
  {a:'testB', 'b.c':'test5', 'b.d':8},
  {a:'testB', 'b.c':'test5', 'b.d':9},
]

[mathiasrw]

Ok - I get it now...

its not working - just leaving a link so I can find it myself: http://jsfiddle.net/40oq02g4/5/

[agershun]

Even more we need option to have expressions like this (like ROLLUP):

[
  {a:'test'},
  {a:'test', 'b.c':'test1', 'b.d':'test2'},
  {a:'test', 'b.c':'test2', 'b.d':'test1'},
  {a:'testB'},
  {a:'testB', 'b.c':'test3', 'b.d':'test4'},
  {a:'testB', 'b.c':'test5'},
  {a:'testB', 'b.c':'test5', 'b.d':8},
  {a:'testB', 'b.c':'test5', 'b.d':9},
]

[mathiasrw]

They are very closely related. Ill look at it in the weekend.

[mathiasrw]

Its not working out for me. I might have to put it on hold and get some fresh inspiration.

[agershun]

Agree, this is not easy. The most hardest case: what to do if there are two arrays in the record:

{a:{b:[1,2], c:[3,4], d:5}}

Ok, let's wait for Muses.

[mathiasrw]

There should be a programming muse/god that you could pray to when strange errors and hard challenges stick to your code... She could be named "Codea" ;-)

As I understand it {a:{b:[1,2], c:[3,4], d:5}} would give

[
    {
        "a.b":1,
        "a.c":3,
        "a.d":5
    },{
        "a.b":1,
        "a.c":4,
        "a.d":5
    },{
        "a.b":2,
        "a.c":3,
        "a.d":5
    },{
        "a.b":2,
        "a.c":4,
        "a.d":5
    }
]

yes?

[agershun]

Permutation can be an option.

Отправлено с iPhone

17 янв. 2016 г., в 15:43, Mathias Rangel Wulff notifications@github.com написал(а):

There should be a programming muse/god that you could pray to when strange errors and hard challenges stick to your code... She could be named "Codea" ;-)

As I understand it the task then {a:{b:[1,2], c:[3,4], d:5}} would give

[ { "a.b":1, "a.c":3, "a.d":5 },{ "a.b":1, "a.c":4, "a.d":5 },{ "a.b":2, "a.c":3, "a.d":5 },{ "a.b":2, "a.c":4, "a.d":5 } ] yes?

— Reply to this email directly or view it on GitHub.