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"]

Fetch records as it's ... and normalize them in your js-code... — Hend El-Sahli, Nov 16 '20 at 10:14
@HendEl-Sahli Is there any simple way to extract directly using sqlite query beacuse in my case data could be in a large quantity so it could slows down my project — Bhupesh Kumar, Nov 16 '20 at 10:35

forpas · Answer 1 · 2020-11-16T12:07:47.560

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.

Thanks for your answer but json_extract() method not found in my case — Bhupesh Kumar, Nov 16 '20 at 10:48
You didn't get me, I asked how to use this query in react native — Bhupesh Kumar, Nov 16 '20 at 14:14

SQLite query to extract unique values from json

1 Answers1