SQLite query to extract unique values from json

Question

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", "value":"value2, value3"}, 
  3    :   {"id":"id3", "value":"value4, value5"}

and I just want to extract those unique value using SQLite query, output I expect is : ["value1","value2","value3","value4","value"]

Answer 1

You can do it with json_extract():

select group_concat(json_extract(col2, '$.value'), ', ') result
from tablename

Result:

> result                                
> -------------------------------------
> value1, value2, value3, value4, value5

Or if you want the result formatted as a json array use json_group_array():

select replace(json_group_array(json_extract(col2, '$.value')), ', ', '","') result
from tablename

Result:

> | result                                         |
> | :--------------------------------------------- |
> | ["value1","value2","value3","value4","value5"] |

If you can' use the JSON1 Extension then you can do it with string functions:

select group_concat(substr(
         col2, 
         instr(col2, '"value":"') + length('"value":"'), 
         length(col2) - (instr(col2, '"value":"') + length('"value":"') + 1)
       ), ', ') result
from tablename

Result:

> result                                
> -------------------------------------
> value1, value2, value3, value4, value5

See the demo.