SQLite JSON Group By – How to Group JSON Data Using a Query

group byjsonquerysqlite

I have a db whose values are as follows:img Can I call the json value of listing_id(23,27….so on) in properties column(with having row value as "listing") using a single query, I would Really Appreciate the help . enter image description here

I'm using sqlite .json extension is not available.what I'm trying to do is::group all the rows having listing_id as a single value(23,27…).is there any other way?

Best Answer

If the content of the properties column is like in your screenshot, it's easy to remove everything from the content except for the pure number IDs:

SELECT REPLACE(REPLACE(properties, '{"listing_id":', ''), '}', '') AS listing,
       *
FROM   ahoy_events;

This will give you the pure numbers from your json column as (new) column listing.

| listing | id | visit_id | ... | properties        |
|---------------------------------------------------|
| 27      | 57 | 2        | ... | {"listing_id":27} |
| 23      | 58 | 2        | ... | {"listing_id":23} |

You can then group by this value.

Was this what you were asking for?

Related Solutions

Postgresql – CRUD operations on json data

Matheus de Oliveira created handy functions for JSON CRUD operations in postgresql. They can be imported using the \i directive. Notice the jsonb fork of the functions if jsonb is your data type.

9.3 json https://gist.github.com/matheusoliveira/9488951

(and while jsonb is not an option for Michael, for those who are using 9.4, there is a jsonb variant: https://gist.github.com/inindev/2219dff96851928c2282)

Operations Provided:

postgres=# SELECT json_append('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
        json_append        
---------------------------
 {"a":1,"b":2,"c":3,"a":4}
(1 row)

postgres=# SELECT json_delete('{"b": 2, "c": 3, "a": 4}', '{b, c}');
 json_delete 
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_update('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
 json_update 
-------------
 {"a":4}
(1 row)

postgres=# SELECT json_merge('{"a": 1}', '{"b": 2, "c": 3, "a": 4}');
     json_merge      
---------------------
 {"b":2,"c":3,"a":4}
(1 row)

Is it possible to store and query JSON in SQLite

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.

Best Answer

Related Solutions

Postgresql – CRUD operations on json data

Is it possible to store and query JSON in SQLite

Related Question