Multi layer JSON query

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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 amount and num of goods when the customer or goods name is different.



I only write the SQL:     select json_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 amount and num by 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <pre></pre> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Multi layer JSON query

Jens Alfke-2
In reply to this post by No.1 Perfect


> On Dec 19, 2019, at 7:45 PM, No.1 Perfect <[hidden email]> wrote:
>
> How can I count the amount&nbsp;and num&nbsp;of goods when the customer or goods&nbsp;name 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 <https://sqlite.org/json1.html#jeach>

—Jens

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