MySQL 5.7 JSON_EXTRACT usage with same key names in JSON array

Question

I have following JSON object stored in MySQL:

[
  {
    "key": "user_agent",
    "value": "Mozilla/5.0 (Windows NT 6.1; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; InfoPath.2; .NET4.0C; .NET4.0E; rv:11.0) like Gecko"
  },
  {
    "key": "language",
    "value": "fr-FR"
  },
  {
    "key": "color_depth",
    "value": 24
  },
  {
    "key": "js_fonts",
    "value": [
      "Arial",
      "Arial Black"
    ]
  }
]

Can you help to SELECT for 2nd or 1st value from that JSON. Optionally where "key" is for example "user_agent". I guess that would be better as value order is not guaranteed. I just can't really figure out, how to use JSON path as described in: https://dev.mysql.com/doc/refman/5.7/en/json-path-syntax.html

To make it easier, here is SQL statement to create sample table:

CREATE TABLE `test` (
  `components` json NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test` VALUES ('[{\"key\": \"user_agent\", \"value\": \"Mozilla/5.0 (Windows NT 6.1; Trident/7.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; InfoPath.2; .NET4.0C; .NET4.0E; r1.0) like Gecko\"}, {\"key\": \"language\", \"value\": \"fr-FR\"}, {\"key\": \"color_depth\", \"value\": 24}, {\"key\": \"js_fonts\", \"value\": [\"Arial\", \"Arial Black\"]}]');

JSON_EXTRACT(components, '$[1]') extracts second array:

{"key": "language", "value": "fr-FR"}

But it's not exactly what I want.

lapkritinis · Accepted Answer · 2020-09-13 11:57:12Z

4

Ok.. it was easier than I thought. At least to extract it:

SELECT JSON_EXTRACT(components, '$[1].value') FROM test;

returns what I want "fr-FR". Or unquoted result with shorter syntax:

SELECT components->>'$[1].value' FROM test;

edited Sep 13, 2020 at 11:57

answered Jan 24, 2017 at 9:45

lapkritinis

2,8144 gold badges22 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

MySQL 5.7 JSON_EXTRACT usage with same key names in 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