Stack Overflow
Stack Overflow

Questions tagged [sqlite-json1]

The json1 extension is a loadable extension that implements eleven application-defined SQL functions and two table-valued functions that are useful for managing JSON content stored in an SQLite database.

The json1 extension is a loadable extension that implements eleven application-defined SQL functions and two table-valued functions that are useful for managing JSON content stored in an SQLite database. The full docs for the extension can be found in JSON1 Extension

61 questions
2
votes
1 answer

Aggregate SQLite query across multiple tables using JSON1

I can't get my head around the following problem. The other day I learned how to use the JSON1 family of functions, but this time it seems to be more of an SQL issue. This is my database setup: CREATE TABLE persons(id INTEGER PRIMARY KEY…
Klemmbonzo
  • 37
  • 3
2
votes
1 answer

How to use sqlite json1 features such as json_extract, json_each, json_tree using ORM DB API of flask-sqlalchemy?

I want to use json_extract, json_tree, json_each features of JSON1 Extension enabled sqlite3 database with flask-sqlalchemy. With reference to link here, I am making ORM query as below: Model class EventHistory(db.Model): timestamp =…
Tushar Srivastava
  • 21
  • 4
2
votes
1 answer

Query a JSON array for multiple values using json1 in sqlite

I am trying to use the json1 extension to query my sqlite database and want to query a json array to match multiple values and return the entry that matches all the values. Example entities: Entity 1: { EntityID: 123, tags: [tag1, tag2,…
Siddharth Sharma
  • 21
  • 1
  • 2
2
votes
1 answer

How to retrieve a value in a json object in sqlite when the key is an empty string?

I am processing an sqlite table which contains json objects. These json objects have keys that are empty strings. How can I retrieve the value? For example: select json_extract('{"foo": "bar", "":"empty"}', '$.foo') as data; -result: "bar" How can…
SimpleNYC
  • 23
  • 4
2
votes
1 answer

Using json_extract to find in all objects in JSON array

How can json_extract be used to look through all objects in an array? It works if you knew the key, but I want to look at every single object and find the one that matches. $.features[0].properties.TMPRIV_ID How to get this to…
Harry
  • 13,091
  • 29
  • 107
  • 167
2
votes
1 answer

How to enable the `JSON1` extension for `SQLite3` in PHP?

I want to try the JSON1 extension for SQLite in PHP7 but it is not enabled by default. So I have compiled a new php_sqlite3.dll with the JSON1 extension enabled as per these instructions but when I try to start PHP I get an error; Warning: PHP…
Nigel Alderton
  • 2,265
  • 2
  • 24
  • 55
2
votes
0 answers

How to upgrade SQLite in MAMP to use JSON1 extension?

I am using latest OS X El Capitan. My MAMP is 3.5 PRO. I am running PHP 7.0.0 in it. You can see the info of the PHP here. I figured that SQLite is available in OS X by default. But the version of this SQLite in OS X was 3.8. JSON1 extension is only…
fozuse
  • 754
  • 2
  • 11
  • 29
1
vote
1 answer

select node value from json column type

A table I called raw_data with three columns: ID, timestamp, payload, the column paylod is a json type having values such as: { "data": { "author_id": "1461871206425108480", "created_at": "2022-08-17T23:19:14.000Z", "geo": { …
arilwan
  • 3,374
  • 5
  • 26
  • 62
1
vote
1 answer

How to insert an element into the middle of an array (json) in SQLite?

I found a method json_insert in the json section of the SQLite document. But it seems to be not working in the way that I expected. e.g. select json_insert('[3,2,1]', '$[3]', 4) as result; The result column returns '[3,2,1,4]', which is correct. But…
Wenwu
  • 45
  • 6
1
vote
1 answer

SQLite3 JSON1 Order by numeric index

I have a table like this: TestTable --------- data (TEXT) All data values are JSON objects like { a:1, b:2, c:3 }. I want to be able to query the database and ORDER BY data->b DESC without a full table scan (indexed). Is this possible in SQLite…
Lucien
  • 776
  • 3
  • 12
  • 40
1
vote
0 answers

SQLite query by json object property within an array

I've recently started using SQLite with the JSON1 extension which allows me to store and query dynamic/json data. Lets take for example the following table and data structure: # documents table: -------------------------------------------- id | …
P.Larich
  • 11
  • 1
  • 1
1
vote
1 answer

SQLite Epoch time query

Could use a bit of help on this. I have a table which stores records in JSON format in the acctinfo column. I can export the JSON content without issues, but the problem i'm running into is with the epoch time. I would like to be able to display my…
sirobione
  • 7
  • 2
1
vote
1 answer

Use a JSON predicate in Sqlite query to get an array item

I am trying to retrieve a single item from a JSON array in Sqlite using a predicate. This is my sample data set: { "book": [ { "author": "Nigel Rees", "category": "reference", "price": 8.95, …
Marek Stejskal
  • 2,698
  • 1
  • 20
  • 30
1
vote
1 answer

How to inner join and parse data as JSON using json1

For example, let's say I've a table called Movie with 2 columns - [id,title] Data: 1, killbill 2, endgame and another table as Cast with 2 columns - [id,name] Data: 1, Uma 1, David 2, Robert Cast table has actors and id is same as movie. The…
helloworld
  • 2,179
  • 3
  • 24
  • 39
1
vote
1 answer

how to extract nested json using sqlite json-extract

How could I extract nested json using sqlite json-extract or other sqlite json command ? Here I'd like to extract given_id "invoices": [{ ........ "items": [{ "given_id": "TBC0003B", ... } ] } ] Thanks.
kkk
  • 95
  • 1
  • 2
  • 11
1
2
3 4 5