On 17 Jun 2017, at 19:29, Igor Tandetnik wrote:

> For the first three (or any fixed N) columns, yes. But I thought you wanted a 
> view that somehow automatically becomes wider or narrower as rows are 
> inserted or deleted in the underlying table. I don't believe such a thing is 
> possible.

That's right, the query has to be build fitting the structure.

> So you already know the answer to your question, then? What else would you 
> like assistance with?

Well, the question for me is, which approach will be faster?

A table with (entity, value) and a query like I posted:

select rec_id
, max(case when col_id = "col-1" then value end) col1
, max(case when col_id = "col-2" then value end) col2
, max(case when col_id = "col-3" then value end) col3
from products where col1 = "ABC"
group by rec_id

or using the JSON1 extension and building a query like:

select rec_id
, json_extract(json_value,"$.col1") as col1 from json_products
, json_extract(json_value,"$.col2") as col2 from json_products
from products, json_each(col1)
where json_each.value = "ABC"

The JSON data might be 20.000 records each JSON object/array containing 5 - 50 

Are there are any benchmarks available?


Robert M. Münch, CEO
M: +41 79 65 11 49 6

Saphirion AG
smarter | better | faster


Attachment: signature.asc
Description: OpenPGP digital signature

sqlite-users mailing list

Reply via email to