How to multiple array of json values in MYSQL? [duplicate]

Question

I've a column that contain json values like

[
  {
    "name": "enabled",
    "value": "true",
    "type": "boolean"
  },
  {
    "name": "limit_amount_per_day",
    "value": "100",
    "type": "text",
    "pattern": "^[0-9]*$",
    "min": "0",
    "max": "100000"
  },
  {
    "name": "limit_amount_per_month",
    "value": "1000",
    "type": "text",
    "pattern": "^[0-9]*$",
    "min": "0",
    "max": "100000"
  },
]

or can be in

[{"name": "enabled", "value": "true", "type": "boolean"}, {"name": "limit_amount_per_day", "value": "100", "type": "text", "pattern": "^[0-9]*$", "min": "0", "max": "100000"}, {"name": "limit_amount_per_month", "value": "1000", "type": "text", "pattern": "^[0-9]*$", "min": "0", "max": "100000"},]

How can I query inside column in sql ?

For example... from that json , i want to get value that contains name 'enabled' so i could use it in another queries.

What i've tried was SELECT * FROM SOME_TABLE where value LIKE '%"name":"enabled", "value": "boolean"%' but it is very error prone .

Anybody can find out way with this?

Duplicate How to search JSON data in MySQL?

esqew
– esqew

2023-09-10 12:19:01 +00:00
Commented Sep 10, 2023 at 12:19 — esqew
– esqew, Commented Sep 10, 2023 at 12:19

Ajax1234 · Accepted Answer · 2023-09-10 15:13:40Z

1

Using json_table:

select k.value from test t 
cross join json_table(t.js, '$[*]' columns (name text path '$.name', value 
    text path '$.value')) k
where k.name = 'enabled'

See fiddle

answered Sep 10, 2023 at 15:13

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

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

Collectives™ on Stack Overflow

How to multiple array of json values in MYSQL? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related