Output JSON object as nested objects

[Slyke]

Problem:

Setting INTO JSON is returning a flattened JSON object.

Code (NodeJS v12.14):

const alasql = require('alasql');

const data = [
  {
    name: 'item1',
    details: {
      stock: 10,
      purchased: 100
    }
  },
  {
    name: 'item2',
    details: {
      stock: 20,
      purchased: 200
    }
  },
  {
    name: 'item3',
    details: {
      stock: 30,
      purchased: 300
    }
  },
]

const res = alasql(`SELECT name, details->stock INTO JSON() FROM ? WHERE details->stock > 11`, [data]);
console.log(res);

Output:

[{"name":"item2","details->stock":20},{"name":"item3","details->stock":30}]

Expected Output (Formatted):

[{
	"name": "item2",
	"details": {
		"stock": 20
	}
}, {
	"name": "item3",
	"details": {
		"stock": 30
	}
}]

I did read: https://github.com/agershun/alasql/wiki/Json and nothing about options for JSON() like there is for CSV()

[Slyke]

I have a workaround for now:

const data = [
  {
    name: 'item1',
    details: {
      stock: 10,
      purchased: 100
    }
  },
  {
    name: 'item2',
    details: {
      stock: 20,
      purchased: 200
    }
  },
  {
    name: 'item3',
    details: {
      stock: 30,
      purchased: 300
    }
  },
]

const res = alasql(`SELECT name, details->stock INTO JSON() FROM ? WHERE details->stock > 11`, [data]);

const resJson = JSON.parse(res);

const jsonifiedResult = [];
resJson.forEach((row) => {
  const outputObject = {};
  Object.keys(row).forEach((objectPath) => {
    const stringObjectSplit = objectPath.split('->');

    let partialMerge = outputObject;
    while (stringObjectSplit.length > 1) {
      const section = stringObjectSplit.shift();
      partialMerge = partialMerge[section] = partialMerge[section] || {};
    }

    partialMerge[stringObjectSplit[0]] = row[objectPath];
  });
  jsonifiedResult.push(outputObject);
})

console.log(jsonifiedResult);
console.log(res); // Original for reference

Output:

[
  { name: 'item2', details: { stock: 20 } },
  { name: 'item3', details: { stock: 30 } }
]
[{"name":"item2","details->stock":20},{"name":"item3","details->stock":30}]

This would be much more efficient if the object was constructed at query time though, instead of using the output after the fact.

[mathiasrw]

I get the idea, but we might need another notation to indicate this. The value of details->stock is 20 - so that is the value

What you describe is to get the details object - but with only certain elements removed.

What would be a good way to indicate that you want the actual object?

[Slyke]

Does INTO OBJECT work? :D