Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

MariaDB Json: Get every key of the whole JSON-column

CREATE TABLE t (j LONGTEXT);

INSERT INTO t (j) VALUES ('{"a":"1"}');

INSERT INTO t (j) VALUES ('{"b":"2"}');

INSERT INTO t (j) VALUES ('{"c":"0", "d":"0"}');

INSERT INTO t (j) VALUES ('{"c":"1", "d":"1"}');

SELECT JSON_KEYS(j) FROM t;
+--------------+
| JSON_KEYS(j) |
+--------------+
| ["a"]        |
| ["b"]        |
| ["c", "d"]   |
| ["c", "d"]   |
+--------------+

I am looking for a list of every keys in the whole table? Is that possible?

Such as

+----------------------+
| ["a", "b", "c", "d"] |
+----------------------+

r even better.

+-----+
| "a" |
| "b" |
| "c" |
| "d" |
+-----+
Tags:

3

Answers


  1. 0

    Depending on what you want, one of:

    # just the values
select distinct k
from t
cross join
json_table(json_keys(j), '$[*]' columns (k text path '$')) j
order by k

    or

    # quoted values
select distinct json_quote(k)
from t
cross join
json_table(json_keys(j), '$[*]' columns (k text path '$')) j
order by k

    or

    # in an array
select json_arrayagg(distinct k order by k)
from t
cross join
json_table(json_keys(j), '$[*]' columns (k text path '$')) j
    Login or Signup to reply.
  2. 0

    This can be done using JSON_TABLE as follows :

    SELECT DISTINCT `Key`
FROM t, 
     JSON_TABLE(
         JSON_KEYS(t.j),
         '$[*]'
         COLUMNS(
           `Key` VARCHAR(10) PATH "$"
         )
      ) d

    Demo here

    Login or Signup to reply.
  3. 0

    You can use JSON_TABLE() along with JSON_KEYS() for version 10.6+ :

    SELECT DISTINCT jt.*
FROM t, 
     JSON_TABLE(JSON_KEYS(j), '$[*]' 
       COLUMNS (
                 labelKey VARCHAR(40) PATH '$'
               )
     ) jt

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search