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
0
votes
1 answer

Anyone used JSON1 extention in ionic angular?

I would like to retrieve SQlite data in JSON format using the JSON1 extension, however when i implement the code, i get the error message error{"message":"sqlite3_prepare_v2 failure: no such function: json_object", "code":5} Is there a plugin or…
user9913710
  • 23
  • 6
0
votes
1 answer

How to fast bulk insert from json data in sqlite in android

I have written this code to insert json data in to sqlite in android its working fine but i have 50000+ row to insert so its taking so much time to insert into sqlite database. So how can I insert this data in fastest way please kindly give me the…
Dinesh Gangwar
  • 17
  • 3
0
votes
0 answers

Can infinity be stored in a SQLite JSON1 column?

Use Case I'm using SQLAlchemy and SQLite for a standalone application (non-web) and I need to store numerical ranges in a database. I'm doing this by saving the start and end of each range in SQLite like so: [[float("-inf"), 0], [1, 1000],...,…
baqyoteto
  • 334
  • 2
  • 9
0
votes
1 answer

SQLite query to extract unique values from json

For use in React Native, Suppose I have a SQLite database table contains col1 primary, col2. Where col1 contains a serial number and col2 contains a JSON like col1 : col2 1 : {"id":"id1", "value":"value1"}, 2 : {"id":"id2",…
Bhupesh Kumar
  • 240
  • 2
  • 14
0
votes
0 answers

sqlite3.OperationalError: undefined symbol: sqlite3_json_init

I have installed the JSON1 extension for sqlite3 with Python. I connect to the database with: conn = sqlite3.connect(DATABASE_PATH) And when I try to load the extension with conn.load_extenstion(PATH_TO_EXTENSION). I get the following…
Amrou
  • 371
  • 1
  • 3
  • 18
0
votes
1 answer

How to access json values when keys vary from one row to another in SQLite?

Trying to make sense of a database written in json format. I managed to convert the main tables into csv format however there are columns within the tables themselves that are written in json format. The table in question describes soccer matches…
Rebecca Vella
  • 31
  • 5
0
votes
0 answers

Returning sqlite3 JSON1 extension output as JSON in python/sqlalchemy

I'm working with sqlite3 JSON1 extension in python3. The following code returns the output as string, but I'd like it to be a JSON object. Is this even possible in sqlite3 WITHOUT using json.loads()? conn = sqlite3.connect(':memory:') c =…
mru
  • 1
  • 2
0
votes
2 answers

How to write a SQL query to pull a value from a nested json object identified by a variable field name

Problem: how to write a sqlite statement to select a value from a nested json object when the needed name is dynamic / variable. It is also important that this can be done from a single sql statement. Eventually, this will be executed from within a…
C0ntr07
  • 51
  • 1
  • 1
  • 9
0
votes
2 answers

Extracting JSON arrays and inserting them as separate rows in a new database

I have a database which uses JSON to store values. CREATE TABLE JSON(name TEXT, value TEXT); I am trying to convert this into a native format. CREATE TABLE NATIVE(name TEXT, KEY1, KEY2, KEY3); The JSON format looks like this: [ …
Michael
  • 9,060
  • 14
  • 61
  • 123
0
votes
1 answer

Extracting data from an array of JSON objects for specific object values

In my table, there is a column of JSON type which contains an array of objects describing time offsets: [ { "type": "start" "time": 1.234 }, { "type": "end" "time": 50.403 } ] I know that I can extract these with JSON_EACH()…
Brad
  • 159,648
  • 54
  • 349
  • 530
0
votes
1 answer

How to concat a json arrays objects values in SQLite

As part of a larger select query I need to extract the values from an json array's objects as a comma separated string. I have managed to get the json array out of the json object: SELECT * FROM (SELECT json_extract(Site.Login, '$.Uris') FROM…
TBK
  • 129
  • 1
  • 2
  • 8
0
votes
1 answer

SQLite search inside a column with JSON inside

I have an SQLite database where a column is called i18n and contains a JSON tree where some localized strings are contained. For example: {"gr":{"name":"NAME_IN_GR"},"ru":{"name":"NAME_IN_RU"}} Now I should make a search for a partial string typed…
danielemm
  • 1,636
  • 1
  • 14
  • 24
0
votes
1 answer

How to join multiple JSON columns in Sqlite

I am looking for the Sqlite syntax to join multiple JSON columns, in the same table. I create a table like:- SQL = "CREATE TABLE BranchStock (branch VARCHAR, oldstock JSON, newstock JSON)" .... I then insert a few items like:- INSERT INTO…
42LeapsOfFaith
  • 146
  • 1
  • 9
0
votes
2 answers

Remove K,V pairs with value 'NULL' from json object in query result

The following gives me a result : {"a":null,"b":99.0,"c":null} I'd like to have {"b":99.0} as a result so I can use the result in a JSON patch. How can I achieve this with sqlite/json1? DROP TABLE IF EXISTS test; CREATE TABLE test ( id INTEGER…
ingo
  • 117
  • 10
0
votes
1 answer

Upgrade sqlite3 to custom package

I have a server running centos7 with sqlite3 installed using Yum/rpm. By default the sqlite package is not compiled with the extension JSON1 which I need. The extension can be enabled when the package is compiled using the flag -DSQLITE_ENABLE_JSON1…
user2863274
  • 13
  • 1
  • 3
1 2 3
4
5