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

SQLite JSON1 example for JSON extract\set

SQLite has now an experimental JSON1 extension to work with JSON fields. The functions to choose from look promising, but I don't get how to use them in the context of a query. Suppose I created the following table: sqlite> create table…
oz123
  • 27,559
  • 27
  • 125
  • 187
13
votes
2 answers

Python - SQLite JSON1 load extension

I want to use the json1 extension for SQLite within Python. According to the official documentation, it should be a loadable extension. I got the json1.c file from the source and compiled it into json1.so as per the official instructions without any…
Ali Kakakhel
  • 173
  • 1
  • 3
  • 10
9
votes
2 answers

Sqllite : search value from json string

I have table name users contain column name user_email. user_email column having data in json format like below. [ { "card_email_id": "98", "card_id": "88", "email": "raj@ccs.sg", "type": "Home" }, { "card_email_id": "99", …
Maulik patel
  • 1,551
  • 8
  • 22
  • 44
7
votes
3 answers

Sqlite: append a new element to an existing array

From the sqlite3 doc select json_insert('{"a":2,"c":4}', '$.e', 99) -- → '{"a":2,"c":4,"e":99}' But how to append a new element to an array? select json_insert('[1,2,3]', ??, 4) -- → '[1, 2, 3, 4]' update someTable set someArray =…
Green
  • 4,950
  • 3
  • 27
  • 34
6
votes
2 answers

Is there a method to check if an array includes one value in SQLite?

Let's say, we have this SQLite table with id=number and tags=text: | id | tags | | ---- | ------------------- | | 1 | ["love","sadness"] | | 2 | ["love"] | | 3 | ["happiness","joy"] | Is there a way…
user10458813
6
votes
1 answer

How do I get nested JSON data out of SQLite with a multi-level group by?

create table store (id integer primary key, name text); create table opening (store integer references store(id), wday text, start integer, end integer); insert into store (name) values ('foo'), ('bar'); insert into opening (store, wday, start,…
Brian H
  • 314
  • 3
  • 13
6
votes
2 answers

Updating JSON in SQLite with JSON1

The SQLite JSON1 extension has some really neat capabilities. However, I have not been able to figure out how I can update or insert individual JSON attribute values. Here is an example CREATE TABLE keywords ( id INTEGER PRIMARY KEY, lang INTEGER…
DroidOS
  • 8,530
  • 16
  • 99
  • 171
6
votes
3 answers

Error: no such function: json_each in SQLite with JSON1 installed

I've installed SQLite3 with JSON1 through brew: brew install sqlite3 --with-json1 --with-fts5 Version: 3.15.2 2016-11-28 19:13:37 bbd85d235f7037c6a033a9690534391ffeacecc8 When running a query, some functions work fine, such as…
sgtdck
  • 1,008
  • 7
  • 15
4
votes
1 answer

how to extract properly when sqlite json has value as an array

I have a sqlite database and in one of the fields I have stored complete json object . I have to make some json select requests . If you see my json the ALL key has value which is an array . We need to extract some data like all comments where…
MAG
  • 2,841
  • 6
  • 27
  • 47
4
votes
1 answer

Loading JSON1 extension with Flutter sqflite

In my Flutter app I'm using sqflite to talk to a local database. I need to peek into JSON data. The JSON1 extension would be ideal for this. However, I can't load the extension in a Flutter app to make it available in my queries since the…
Jesse de Wit
  • 3,867
  • 1
  • 20
  • 41
4
votes
0 answers

How to enable extension loading in SQLite3?

I want to play with the JSON1 extension for SQLite3 but when I do the following; loadExtension('json1.dll'); ?> I get the error; Warning: SQLite3::loadExtension(): Unable to load extension at 'ext\json1.dll'…
Nigel Alderton
  • 2,265
  • 2
  • 24
  • 55
4
votes
0 answers

Problems compiling on "architecture x86_64" for JSON1 extension on SQLite

There are lots of threads that have similar questions, but many deal with c++ code and have c++ solutions. However, from what I gather on the various threads, I might be missing a library when gcc is trying to link libraries. I don't know which…
tempomax
  • 773
  • 4
  • 10
  • 24
3
votes
1 answer

SQLite on Ubuntu 16.04 with JSON1

Is there a SQLite distribution for Linux/Ubuntu that is compiled with the JSON1 extension. The instructions on the SQLite JSON1 page only explain how to load the extension after compiling it - a digression I would like to avoid.
DroidOS
  • 8,530
  • 16
  • 99
  • 171
3
votes
1 answer

JSON fields from sqlite3

I have a json field in a sqlite3 collection. My schema looks like: CREATE Table Animals( id int, sounds json, name string ) I understand the go-sqlite interface does not support the json datatype explicitly. However, my json is quite…
Mittenchops
  • 18,633
  • 33
  • 128
  • 246
2
votes
1 answer

extract value from JSON object using SQLite and the json_tree function

I have a table (named, patrons) that contains a column (named, json_patron_varfields) of JSON data--an array of objects that looks something like this: [ { "display_order": 1, "field_content": "example 1", "name": "Note", …
ray_voelker
  • 495
  • 3
  • 12
1
2 3 4 5