0

Situation

I have a table in a MariaDB database. This table has a LONGTEXT column which is used to store a JSON array (read more about this topic in MariaDB JSON Data Type).

Question

I would like to extract values from the JSON array, based on a certain key. How do I achieve this with MariaDB (or MySQL)?

Example

Here's the simplified table thing (just for demo purposes):

id thing_name examples
0 fruit [{"color": "green","title": "Apple"},{"color": "orange","title": "Orange"},{"color": "yellow","title": "Banana"}]
1 car [{"color": "silver","title": "VW"},{"color": "black","title": "Bentley"},{"color": "blue","title": "Tesla"}]

My goal is to extract all title values from the JSON array.

gru
  • 2,319
  • 6
  • 24
  • 39

1 Answers1

0

You can use JSON_EXTRACT for this task (works for both MariaDB and MySQL). This function also supports wildcards, as described in the docs:

Paths can contain * or ** wildcards

Depending on whether you have multiple levels of data (e.g. single document vs array), either a single or double asterisk wildcard should be used:

JSON_EXTRACT(json,'$**.key')

json is a valid JSON document (e.g. a column), key is the lookup key used.

For your example

In order to find all title values in your JSON array, use the following query:

SELECT id, thing_name, JSON_EXTRACT(examples, '$**.title') as examples_titles FROM thing
id thing_name examples_titles
0 fruit ["Apple", "Orange", "Banana"]
1 car ["VW", "Bentley", "Tesla"]
gru
  • 2,319
  • 6
  • 24
  • 39
  • 2
    It takes seconds to answer your own question? 0_0 – Kuro Neko May 18 '22 at 08:19
  • 2
    And all of this is available in the manual ? – RiggsFolly May 18 '22 at 08:21
  • 1
    @the_coding_cat You can ask questions on Stack Overflow in [Q&A style](https://stackoverflow.blog/2011/07/01/its-ok-to-ask-and-answer-your-own-questions/?_ga=2.90169755.1022439125.1652858903-73467118.1643011997), as they call it (i.e. already provide the answer when formulating the question). It's intended for situations where you've found a solution for your problem already and want to share this finding with the world. Or ideally, even find a better solution from other answers in the future. :) – gru May 18 '22 at 08:25
  • Oh... I see. Okay, I'm just flabbergasted. Anyways as long as it will help the future readers and as long as the answer is really helpful, then there's nothing to worry about. – Kuro Neko May 18 '22 at 08:29
  • 1
    The solution does not match the task, it seems. The task "extract all title values from the JSON array" should match "one value per output row" whereas you obtain an array with title values, i.e. one output row for source row. – Akina May 18 '22 at 08:47