CODEWITHSUNDEEP
mysqljsonextract
ShyProgrammer
ShyProgrammer

Reputation: 1

JSON_EXTRACT multidimensional data from MySQL

I have a table, structure as below.

CREATE TABLE `layout` (
  `id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `structure` json NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The field named structure, holds data as below.

[{"Field":"Name","Type":"Text"},{"Field":"DOB","Type":"Date"}]

I want to run query where I give "Field" value and I get "Type" value.

Upvotes: 0

Views: 494

Answers (1)

wchiquito
wchiquito

Reputation: 16569

Try:

SET @`Field` := 'Name';

SELECT
  `id`,
  JSON_UNQUOTE(
    JSON_EXTRACT(
      `structure`,
      JSON_UNQUOTE(
        REPLACE(
          JSON_SEARCH(
            `structure`,
            'one',
            @`Field`,
            NULL,
            '$[*].Field'
          ),
          'Field',
          'Type'
        )
      )
    )
  )
FROM
  `layout`;

See db-fiddle.

Upvotes: 1

Related Questions