SQLite Forum

Merge Data

Merge Data

(1) By pippo1468 on 2021-06-26 23:22:43 [link] [source]


is it possible to merge data into array?

for example i have two result from two different query (i can also ava one query with left join)

{ "columns": [ { "id": 1, "columntitle": "First column", "state": "active" }, { "id": 2, "columntitle": "Second column, "state": "active" } ], "tile": [ { "tiletitle": "First tile", "autore": "John ", "idcolumn": "1", "message": "mymessage" } ] }

i would like it to be :

{ "columns": [ { "id": 1, "columntitle": "First column", "state": "active" "tile": [ { "tiletitle": "First tile", "autore": "John ", "idcolumn": "1", "message": "mymessage" } ] }, { "id": 2, "columntitle": "Second column, "state": "active" "tile": [] } ] }

Here is my code:

exports.columnandtile = async (req, res) => {

const columns = await knex('column')

const tile = await knex('tile').whereIn('idcolumn', columns.map(column => column.id));

res.json({columns:columns,tile:tile}) }

Thank you!

(2.1) By Larry Brasfield (larrybr) on 2021-06-27 15:04:59 edited from 2.0 in reply to 1 [source]

is it possible to merge data into array?

Yes. That and many other slicing, dicing and assembly operations are possible with data in an array.

Your question reminds me of when, as a child, I was told that drinking a glass of grape juice with a fork was impossible. It took me about an hour to disprove that.

I suggest a different question: What are the practical ways of storing and manipulating a collection of data values having the same classification? As you have found, as many database designers eventually discover, and as database theorists have long taught, a lot of extra work results from trying to store multiple items all glommed together when they must be manipulated separately.

As soon as you find yourself having to disassemble and reassemble a set of glommed values, you need to be thinking about how to represent the data in the database structure (aka "schema") so that set operations expressed in SQL can be performed to effect the same logic.

For your specific operations, you might find that the json_each() and json_tree() table-valued functions allow you to perform the manipulations you desire with SQL rather than a sequence of queries generated by procedural code. Or you may find that those functions make it easy to decompose your glommed data into temporary tables to support the shuffling you wish to do, followed by recomposition into array values. Your study of how that would work may suggest a schema more amenable to the operations you intend to support.

If JSON output is valued, that could be accomplished with VIEWs defined on a more finely grained schema.