Mariadb query and extract JSON data

Question

I have a MaraiDB (10.2.14) database containing a table where in one column JSON data is stored.

I'm puzzled as how to extract data from this column.

Example Data

SELECT 1 AS ID
     , '[{"name":"x","score":2},{"name":"y", "score":8},{"name":"z","score":3}]' AS REPLY
UNION ALL
SELECT 2 AS ID
     , '[{"name":"x","score":5},{"name":"y", "score":4},{"name":"z","score":3}]' AS REPLY
UNION ALL
SELECT 3 AS ID
     , '[{"name":"x","score":2},{"name":"y", "score":2},{"name":"z","score":6}]' AS REPLY
UNION ALL
SELECT 4 AS ID
     , '[{"name":"x","score":5},{"name":"y", "score":8},{"name":"z","score":6}]' AS REPLY

So how would I find all entries having "name":"x" and a "score":5. Additionally I need to get the "score" value of the "name":"y" of that entry.

My current dirty approach is

WITH JT1 AS (
        SELECT 1 AS ID
             , '[{"name":"x","score":2},{"name":"y", "score":8},{"name":"z","score":3}]' AS REPLY
        UNION ALL
        SELECT 2 AS ID
             , '[{"name":"x","score":5},{"name":"y", "score":4},{"name":"z","score":3}]' AS REPLY
        UNION ALL
        SELECT 3 AS ID
             , '[{"name":"x","score":2},{"name":"y", "score":2},{"name":"z","score":6}]' AS REPLY
        UNION ALL
        SELECT 4 AS ID
             , '[{"name":"x","score":5},{"name":"y", "score":8},{"name":"z","score":6}]' AS REPLY
)
SELECT ID
     , REGEXP_REPLACE(
         REGEXP_REPLACE( EXTRACTED, '^.*"y",\\s', '')
       , '[,\\]].*$', '') AS Y
     , EXTRACTED
FROM (
        SELECT ID
             , JSON_EXTRACT(REPLY, '$[*].name','$[*].score') EXTRACTED
        FROM JT1
) JT2
WHERE EXTRACTED RLIKE '"x", 5\\b'
;

So I first extract "name" and "score" which gives me column data like ["x", 5, "y", 4, "z", 3]. With that I do some nasty REGEXP search & replaces.

I feel there must be a better way.

I tried using COLUMN_CREATE, but "COLUMN_CREATE" seems not to be able to accept the result from JSON_EXTRACT as input. Now that I think about it, this seems logical as "name" and "score" here are properly ordered, but can I be sure it's always that sequence?

Can anyone give me a hint how to do this better?

wchiquito · Accepted Answer · 2018-08-04 05:24:20Z

2

If I understand what you need, a query like the following may be useful:

WITH `JT1` AS (
  SELECT 1 AS `ID`
  , '[{"name":"x","score":2},{"name":"y", "score":8},{"name":"z","score":3}]' AS `REPLY`
  UNION ALL
  SELECT 2 AS `ID`
  , '[{"name":"x","score":5},{"name":"y", "score":4},{"name":"z","score":3}]' AS `REPLY`
  UNION ALL
  SELECT 3 AS `ID`
  , '[{"name":"x","score":2},{"name":"y", "score":2},{"name":"z","score":6}]' AS `REPLY`
  UNION ALL
  SELECT 4 AS `ID`
  , '[{"name":"x","score":5},{"name":"y", "score":8},{"name":"z","score":6}]' AS `REPLY`
)
SELECT
  `ID`,
  `REPLY`,
  JSON_VALUE(
    `REPLY`,
    JSON_UNQUOTE(
      REPLACE(
        JSON_SEARCH(`REPLY`, 'one', 'y', NULL, '$[*].name'),
        'name',
        'score'
      )
    )
  ) `"name":"y"`
FROM
  `JT1`
WHERE
  JSON_VALUE(
    `REPLY`,
    JSON_UNQUOTE(
      REPLACE(
        JSON_SEARCH(`REPLY`, 'one', 'x', NULL, '$[*].name'),
        'name',
        'score'
      )
    )
  ) = 5;

See dbfiddle.

answered Aug 4, 2018 at 5:24

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

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

2 Comments

Skeeve Over a year ago

Thanks for that. I see that it works, but I do not understand it (yet). Unfortunately I'm not yet eligible for upvoting. I hope someone will do it for me.

Sam_Butler Over a year ago

@Skeeve Done. You can mark it as an answer though, right?

Collectives™ on Stack Overflow

Mariadb query and extract JSON data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related