json - 如何查询sqlite json1

Question

我是使用带有 JSON1 的 SQLite 3 的新手。我想查询json文件。

这是我的 json 文件

{
    "customer": {
        "_id": "6a7e5fbd48a14d39aedb23ab104c1d44",

    },
    "invoices": [{
            "_id": "12db73e71874f0db72bf1a44de02ee69",
            "invoice": 215,
            "payments": [{
                    "invoice_id": "12db73e71874f0db72bf1a44de02ee69",
                    }
            ],
            "items": [{
                    "invoice_id": "12db73e71874f0db72bf1a44de02ee69",
                    "given_id": "TBC0003B",
                    }
            ]
        }, {
            "_id": "5feade559d6ff105f6c8fce7d6e4278f",
            "invoice": 149,
            "payments": [{
                    "invoice_id": "5feade559d6ff105f6c8fce7d6e4278f",
                    }
            ],
            "items": [{
                    "invoice_id": "5feade559d6ff105f6c8fce7d6e4278f",
                    "given_id": "TBC0002B",
                    }
            ]
        }, {
            "_id": "28cfd52546e7527b8bb1d6bc0b92ae44",
            "invoice": 136,
            "payments": [{
                    "invoice_id": "28cfd52546e7527b8bb1d6bc0b92ae44",
                    }
            ],
            "items": [{
                    "invoice_id": "28cfd52546e7527b8bb1d6bc0b92ae44",
                    "given_id": "TBC0002B",
                    }
            ]
        }, {
            "_id": "11377999af6610dd9956e4608acd6953",
            "invoice": 117,
            "payments": [{
                    "invoice_id": "11377999af6610dd9956e4608acd6953",
                    }
            ],
            "items": [{
                    "invoice_id": "11377999af6610dd9956e4608acd6953",
                    "given_id": "BAP0009A",
                    }, {
                    "invoice_id": "11377999af6610dd9956e4608acd6953",
                    "given_id": "FSS0024B",
                    }, {
                    "invoice_id": "11377999af6610dd9956e4608acd6953",
                    "given_id": "DJX0001E",
                    }, {
                    "invoice_id": "11377999af6610dd9956e4608acd6953",
                    "given_id": "LPHEHSM",
                    }, {
                    "invoice_id": "11377999af6610dd9956e4608acd6953",
                    "given_id": "MSN0037",
                    }, {
                    "invoice_id": "11377999af6610dd9956e4608acd6953",
                    "given_id": "MSO0008.6.5",
                    }, {
                    "invoice_id": "11377999af6610dd9956e4608acd6953",
                    "id": "TBC0001A",
                    }
            ]
        }
    ]
}

当我查询如下：

sqlite> SELECT json_extract(data, '$.customer._id') , A.value , B.value FROM dataset,json_tree(data) AS A,json_tree(data) AS B WHERE (A.key='invoice' and A.value != 0) AND (B.key='given_id' and B.value NOT LIKE '6%');

我得到的结果为

149,MSO0008.6.5 6a7e5fbd48a14d39aedb23ab104c1d44,149,TBC0001A 6a7e5fbd48a14d39aedb23ab104c1d44,136,TBC0003B 6a7e5fbd48a14d39aedb23ab104c1d44,136,TBC0002B 6a7e5fbd48a14d39aedb23ab104c1d44,136,TBC0002B 6a7e5fbd48a14d39aedb23ab104c1d44,136,BAP0009A 6a7e5fbd48a14d39aedb23ab104c1d44,136,FSS0024B 6a7e5fbd48a14d39aedb23ab104c1d44,136,DJX0001E 6a7e5fbd48a14d39aedb23ab104c1d44,136,LPHEHSM 6a7e5fbd48a14d39aedb23ab104c1d44,136,MSN0037 6a7e5fbd48a14d39aedb23ab104c1d44,136,MSO0008.6.5 6a7e5fbd48a14d39aedb23ab104c1d44,136,TBC0001A。. . DJX0001E 6a7e5fbd48a14d39aedb23ab104c1d44,136,LPHEHSM 6a7e5fbd48a14d39aedb23ab104c1d44,136,MSN0037 6a7e5fbd48a14d39aedb23ab104c1d44,136,MSO0008.6.5 6a7e5fbd48a14d39aedb23ab104c1d44,136,TBC0001A . . . DJX0001E 6a7e5fbd48a14d39aedb23ab104c1d44,136,LPHEHSM 6a7e5fbd48a14d39aedb23ab104c1d44,136,MSN0037 6a7e5fbd48a14d39aedb23ab104c1d44,136,MSO0008.6.5 6a7e5fbd48a14d39aedb23ab104c1d44,136,TBC0001A . . .

其实我想得到的结果是：

6a7e5fbd48a14d39aedb23ab104c1d44,215,TBC0003B 6a7e5fbd48a14d39aedb23ab104c1d44,149,TBC0002B 6a7e5fbd48a14d39aedb23ab104c1d44,136,TBC0002B 6a7e5fbd48a14d39aedb23ab104c1d44,117,BAP0009A 6a7e5fbd48a14d39aedb23ab104c1d44,117,FSS0024B 6a7e5fbd48a14d39aedb23ab104c1d44,117,DJX0001E 6a7e5fbd48a14d39aedb23ab104c1d44,117,LPHEHSM 6a7e5fbd48a14d39aedb23ab104c1d44,117,MSN0037 6a7e5fbd48a14d39aedb23ab104c1d44,117,MSO0008.6.5 6a7e5fbd48a14d39aedb23ab104c1d44,117 ,TBC0001A

你能教我怎么做吗？谢谢。

标签： jsonsqlite