Re: [sqlite] Multi layer JSON query

[Jens Alfke]

> On Dec 19, 2019, at 7:45 PM, No.1 Perfect <757171...@qq.com> wrote:
> 
> How can I count the amountand numof goods when the customer or 
> goodsname is different.

Querying the contents of arrays is kind of complicated. SQL doesn't understand 
arrays, so the query has to use a "table-valued function", json_each, that 
makes the array appear to be a table where each item is a row. Then you can use 
a JOIN to query that "table".

The documentation has examples: https://sqlite.org/json1.html#jeach 


—Jens

PS: Your email program is turning nonbreaking spaces into "", which makes 
your message hard to read. Please try to fix that.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi layer JSON query

[Robert Hairgrove]

On 20.12.19 04:45, No.1 Perfect wrote:

I've read the full page instructions of JSON1, But I don't know how to use 
JSON1 to query multiple layers JSON data.


The Data as follows :


{
 id: 1,
 data: {
  customer: 1,
  goods: [
{ id: 1, name: "apple", price: 12, num: 10, 
amount: 120 },
{ id: 2, name: "perl", price: 10, num: 5, 
amount: 50 }
  ]
 }
}


(snipped...)

Two suggestions come to mind:

1. Normally, JSON data is not displayed directly in a web page, so all 
the  (entity references for non-breaking space) shouldn't be in 
there (and probably is not valid JSON, anyway).


2. If you do need to display the JSON code for some reason, use regular 
spaces and line feeds and wrap it in HTML  tags.


Unfortunately, I never used JSON queries with SQLite, so others will 
have to help after this point.


HTH,
Bob Hairgrove

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multi layer JSON query

[No.1 Perfect]

My English is poor, please forgive me.



The first time I use SQLite database.


I've read the full page instructions of JSON1, But I don't know how to use 
JSON1 to query multiple layers JSON data.


The Data as follows :


{
 id: 1,
 data: {
  customer: 1,
  goods: [
{ id: 1, name: "apple", price: 12, num: 10, 
amount: 120 },
{ id: 2, name: "perl", price: 10, num: 5, 
amount: 50 }
  ]
 }
}

{
 id: 2,
 data: {
  customer: 2,
  goods: [
{ id: 1, name: "apple", price: 12, num: 10, 
amount: 120 },
{ id: 2, name: "perl", price: 10, num: 5, 
amount: 50 },
{ id: 3, name: "banana", price: 10, num: 5, 
amount: 50 }
  ]
 }
}


{
 id: 3,
 data: {
  customer: 2,
  goods: [
{ id: 1, name: "apple", price: 12, num: 10, 
amount: 120 }
  ]
 }
}




How can I count the amountand numof goods when the customer or 
goodsname is different.



I only write the SQL:  
selectjson_extract(data,'$.goods')from table_name where 
customer = 2



When I got the data, I use the programming language to loop the results and add 
amountand numby myself.



Can you help me, tell me how I should write this.


And thanks for your help, your sqlite database is good database, I will 
continue to use it on my program.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users