56

I need to store JSON objects in a SQLite database, and then do complex queries on it.

I did a table like this:

+--------------------------------------+
|document |  property | string | number|
+--------------------------------------+
|foo      |  "title"  | "test" |       | 
+--------------------------------------+
|foo      |  "id"     |        |  42   | 
+--------------------------------------+
|bar      |  "id"     |        |  43   | 
+--------------------------------------+

for the two objects

foo {"title": "test", "id": 42} 
bar {id: 43}

But I can't do "AND" queries, like:

SELECT DISTINCT id  FROM table WHERE title = "test" AND id = 42 

as you see, the part after the "WHERE" is total nonsense, but I have no idea of how to create a query that would do what I want.

So do you think there is a better way to store my data, or a workaround to do an "AND" query?

And of course, the JSON can contain any property, so I can't create a table with columns for each properties.

I am using WebSQL, which is SQLite without extensions.

I know my question is pretty specific, but could you help me?

4
  • 2
    If that is really important for your application you are probably better off using either a real document store ("NoSQL") or e.g. switch to Postgres which combines very efficient JSON storage with strong relational features.
    – user1822
    Nov 26, 2015 at 15:16
  • 4
    Read the documentation.
    – CL.
    Nov 26, 2015 at 16:27
  • You have to accounts and should merge them: dba.stackexchange.com/users/81459/tuxlu and dba.stackexchange.com/users/81513/tuxlu You can use this help page: dba.stackexchange.com/help/merging-accounts Nov 27, 2015 at 10:33
  • Aside from the problem of using JSON natively in a database, the example database is not normalized. So of course, this makes a select query problematic. Instead of having (document, property, string, number), it should be (document, property, type, value) with value parsed at runtime. From there, it is trivial to create a json object manually. One can opt for "native" json support (like sqllite JSON extensions), but that just masks a deeper design issue IMO. Jan 21, 2022 at 2:51

4 Answers 4

54

SQLite 3.9 introduced a new extension (JSON1) that allows you to easily work with JSON data .

Also, it introduced support for indexes on expressions, which (in my understanding) should allow you to define indexes on your JSON data as well.

10

PostgreSQL has some nice features for JSON storage. You can select the JSON values by doing

SELECT DISTINCT FROM TABLE WHERE foo->title=test AND id=42

You can also index the specific keys on the JSON object if you use the jsonb type.

4
  • 3
    Is there any 1:1 equivalent to this in Sqlite3 these days in 2020+? Jun 19, 2020 at 7:53
  • 17
    Doesn't answer OP question... not about PG Sep 15, 2020 at 23:00
  • Did you consider that maybe OP was already using Postgres in production and wanted to mimic PSQL's capabilities for testing ?
    – jdkealy
    Sep 25, 2020 at 22:27
  • 2
    @NickSweeting in 2022 the -> and ->> operators work in sqlite sqlite.org/json1.html#jptr select foo->'title' from atable
    – Matt
    May 4, 2022 at 1:39
7

The existing answers for this deal with actually storing the OP's data as JSON (which may have been a better solution to his underlying problem).

However, the actual question was how to get find documents in the EAV-style table provided, based on multiple properties. So, perhaps an answer to that question would be useful.

The standard SQL way of doing what you are saying is INTERSECTION.

(SELECT document FROM table WHERE property="id" AND number=43)
INTERSECTION 
(SELECT document FROM table WHERE property="title" AND string="test")

You can also do this with self joins if you prefer.

SELECT t1.document
FROM table as t1, table as t2
WHERE t1.document = t2.document
AND t1.property="id" AND t1.number=43
AND t2.property="title" AND t2.string="test"

Of course the more "correct" and arguably most efficient way would be to just make a column for each property you need, like 'id' and 'title'.

1

You can import the json into a table with a single row and one column and use the built in json functions to extract the values you need.

Based on the given SQL

SELECT DISTINCT id  FROM table WHERE title = "test" AND id = 42 

and the provided foo object

{"title": "test", "id": 42}

I assume that you have an array of json objects like this

[
  {"title": "test 1", "id": 42},
  {"title": "test 2", "id": 43}
]

In my case i had a pmd json output with an array of json objects ([finding-1, finding-2]). Each array element finding contains another json object code which contains another json object target

array : [
   finding-1 { ... }
                 |
                 |--- code : { ... }
                        |
                        |--- target { ... }
   ,finding-2 { ... }
   ,finding-3 { ... }
]

similar to this

[
 {"resource": "/C:/src/one.cls",
  "code": {
        "value": "AvoidGlobalModifier",
        "target": {
            "path": "/latest/pmd_rules_apex_bestpractices.html",
            "fragment": "avoidglobalmodifier"
        }
    },
    "severity": 4,
    "message": "Avoid using global modifier ..."
},
{"resource": "/C:/src/two.cls",
    "code": {
        "value": "ApexSOQLInjection",
        "target": {
            "path": "/latest/pmd_rules_apex_bestpractices.html",
            "fragment": "apexsoqlinjection"
        }
    },
    "severity": 4,
    "message": "... escape variables merged in DML query ..."
}] 

I created table pmd_report where i copied the json-array into the column pmd_output in a single record. The table looks like this

CREATE TABLE "pmd_report" (
    "id"    INTEGER,
    "import_date"   TEXT,
    "pmd_output"    TEXT
)

To extract the values from the json array and the json objects this SQL statement can be used

SELECT 
    json_extract(json_parsed, '$.code.target.fragment') as code_target_fragment, 
    json_extract(json_parsed, '$.severity') as severity, 
    json_extract(json_parsed, '$.message') as message, 
    json_extract(json_parsed, '$.resource') as resource, 
    json_extract(json_parsed, '$.code.value') as code_value, 
    json_extract(json_parsed, '$.code.target.path') as code_target_path 
FROM (
    SELECT jsonEach.Value as json_parsed
        FROM pmd_report,json_each(pmd_report.pmd_output, '$') as jsonEach 
) as subquery;

To store the extracted json you can insert the rows into a new table like this

INSERT INTO pmd_report_2 (fragment, severity, message, startLineNumber, endLineNumber, 
        resource, 
        code_value, 
        code_target_path
        )
SELECT code_target_fragment, severity, message, 
       startLineNumber, endLineNumber, substr(resource, 43) as resource, 
       code_value,  code_target_path
FROM (
    SELECT 
        json_extract(json_parsed, '$.code.target.fragment') as code_target_fragment, 
        json_extract(json_parsed, '$.severity') as severity, 
        json_extract(json_parsed, '$.message') as message, 
        json_extract(json_parsed, '$.startLineNumber') as startLineNumber,
        json_extract(json_parsed, '$.endLineNumber') as endLineNumber,
        json_extract(json_parsed, '$.resource') as resource, 
        json_extract(json_parsed, '$.code.value') as code_value, 
        json_extract(json_parsed, '$.code.target.path') as code_target_path 
    FROM (
            SELECT jsonEach.Value as json_parsed
            FROM pmd_report,json_each(pmd_report.pmd_output, '$') as jsonEach 
    ) as subquery ) 
as subquery_2;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.