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
1
vote
1 answer

SQLite JSON query to count number of items in nested list

I'm trying to query a database in which a column contains JSON data. I am using python and the json1 extension to pull the queries into my python code. A sample of the data within the json inside the database is below. "perf": { …
dtk
  • 13
  • 2
1
vote
2 answers

Query json_extract with PDO and SQLIte db

Try to adopt JSON in database because i have data not fixed. i can query well from terminal, and need to write same query to php script. i have spent a lot of time before ask. example: sqlite> select json_extract(events.interni, '$') from events…
sundsx
  • 588
  • 9
  • 27
1
vote
1 answer

Filter rows based on contents in JSON array

I have a SQLite database which contains a table that has a text column. In this column I store a JSON array of strings. This is the table, shortened to include only what's relevant: CREATE TABLE "recipePreview" ( "id" INTEGER PRIMARY KEY…
iamsim.me
  • 560
  • 4
  • 17
1
vote
1 answer

Setting value of boolean columns based on existence of value in set

I have a SQL table of the format (INTEGER, json_array(INTEGER)). I need to return results from the table that have two boolean columns. One is set to true iff a 1 appears in the json_array, and the other true iff a two appears in the array.…
fredhotdog
  • 13
  • 2
1
vote
0 answers

Sqlite order by clause json_extract gives malformed JSON error

I have some data in a table in sqlite database. The table has only one column with data as JSON records. sqlite> .schema feeds3 CREATE TABLE feeds3 (data text NOT NULL); When I am querying from the table using json_extract I can see the data…
dks551
  • 1,113
  • 1
  • 15
  • 39
1
vote
2 answers

How to use Android NDK SQLite3 extension json1

I am sorry that I lack English first. I compiled it because I needed SQLite3 extension JSON1 in Android NDK gcc -Os -I. -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_JSON1 \ -DSQLITE_DEFAULT_MEMSTATUS=0 -DSQLITE_USE_ALLOCA …
SeongJae Lee
  • 33
  • 9
1
vote
1 answer

Perform Aggregations using JSON1 and SQLite3 in Json Objects

I just started using SQLite 3 with JSON1 support. I already have created a database that consists of several attributes. One of these attributes is a json object. What I want to do is perform aggregations within this object. Running the following…
Giorgos Perakis
  • 153
  • 2
  • 12
1
vote
0 answers

Android SQLite insert JSON in TEXT column with escaping double quotes

I am using SQLite in an Android app which stores, amongst other things, a table that records the mood of the user. The table schema is shown below CREATE TABLE moods ( dow INTEGER, tsn INTEGER, lato INTEGER, agitation TEXT DEFAULT '{}', …
DroidOS
  • 8,530
  • 16
  • 99
  • 171
1
vote
0 answers

Working with arrays with the SQLite/json1 extension (append, tail)

The json1 extension of sqlite3 allows querying and manipulating JSON-values (stored TEXT fields). I want to append a new element to a JSON-array residing inside some JSON-value (in a SQLite database). Unfortunately the support for JSON-arrays seems…
lambdapower
  • 1,022
  • 6
  • 12
1
vote
1 answer

Is there any way to escape dot in path Json1 Sqlite

It seems that if I have key test1.test2 there is no way to escape this dot to use this key as is. Expression: sqlite> update collections set json_nodes = (select json_set(json(collections.js on_nodes), '$.test.test1', json('123')) from…
Kanstantsin Chernik
  • 11
  • 1
1
vote
1 answer

How to set json array index as bound parameter in sqlite3?

I am using the C-API for SQLite3 and the json1 extension. Within the database, a list of integers is stored as a json_array. I want to create a C integer array from the json_array using the json_extract function. I am looping over each value in the…
Matt P
  • 2,287
  • 1
  • 11
  • 26
0
votes
1 answer

Convert triple-triple to object-table in Sqlite JSON1

I have an Sqlite table triples that contains triple information in { id, rel, tgt } format [1]. I would like to create a view that exposes this triple-format data to "object format", which is more easily consumed by applications reading from this…
Róisín Grannell
  • 2,048
  • 1
  • 19
  • 31
0
votes
1 answer

Is it possible to use json features of Sqlite with Entity Framework Core

From what I have found on the web, Sqlite has a Json extension which support the Json operators -> and ->> but that it's not included by default. Is there a way to enable this extension when using Entity Framework Core? My situation is, I'm using…
Mog0
  • 1,689
  • 1
  • 16
  • 40
0
votes
1 answer

json_set returns desired result (in SQLITE Browser) but does not update the table

This is my table: CREATE TABLE orders( id integer primary key, order_uid text unique, created_at text, updated_at text, created_by text, updated_by…
Barry the Platipus
  • 9,594
  • 2
  • 6
  • 30
0
votes
1 answer

Using json_extract in sqlite to pull data from parent and child objects

I'm starting to explore the JSON1 library for sqlite and have been so far successful in the basic queries I've created. I'm now looking to create a more complicated query that pulls data from multiple levels. Here's the example JSON object I'm…
user7711581
  • 3
  • 3
1 2
3
4 5