mariadb sql select distinct values from json array

Question

The database version mariadb 10.3.16 I have a JSON of objects labels: an array of strings

So when I am querying just the labels value, I have the result

["label1", "label7"]
["label2", "labeltest"]
["label1", "labeltest"]

How can I extract only distinct values from here?

markusjm · Accepted Answer · 2020-01-10 06:46:48Z

3

You can use recursive CTEs and JSON functions to extract the label values.

Here's an example query that extracts the unique values from JSON arrays in a table:

WITH RECURSIVE labels AS (
  SELECT 0 AS depth, JSON_VALUE(data, CONCAT('$[', 0, ']')) AS data
    FROM t1
    WHERE JSON_LENGTH(t1.data) > 0
  UNION
  SELECT l.depth + 1, JSON_VALUE(t1.data, CONCAT('$[', l.depth + 1, ']')) AS data
    FROM t1, labels as l
    WHERE JSON_LENGTH(t1.data) - 1 > l.depth
)
SELECT data FROM labels;

answered Jan 10, 2020 at 6:46

markusjm

2,6731 gold badge14 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mariadb sql select distinct values from json array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related