Retrieving JSON array elements based on their values in MySQL

Question

I have a json object stored in MySQL as:

{"PhysicalAddresses": {
"Entry": [
{"Key": "Home", "City": "City1", "State": "ST", "Street": "Some old street", "PostalCode": "00000", "CountryOrRegion": null},
{"Key": "Business", "City": "City2", "State": "ST", "Street": "A much nicer street", "PostalCode": "00000", "CountryOrRegion": null}
]}}

I want to grab the business address: something like

json_extract(column,"$.PhysicalAddresses.Entry[*](where Key=Business)")

What can I put into the "where Key=Business" to make this work, or is there no way to search JSON in MySQL this way?

Thanks

Bill Karwin · Accepted Answer · 2019-01-23 16:15:08Z

1

The JSON Path Syntax allows you to search for a value or subdocument at a path, but it doesn't have any syntax for testing values found at a given path.

This is a case where NOT using JSON would be a better approach. Store each entry in a row of a separate table, and the fields of the entry as independent columns. Then the query is much simpler:

SELECT * FROM PhysicalAddresses WHERE `Key` = 'Business'

It's ironic that so many people say that they use JSON for flexibility. It does give you flexibility in allowing you to insert a variety of data formats, but you sacrifice flexibility on the ways to query the data subsequently.

answered Jan 23, 2019 at 16:15

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

Comments

Dave Stokes · Accepted Answer · 2019-01-23 17:11:08Z

1

You can reach the array you want with SELECT JSON_EXTRACT(column,"$.PhysicalAddresses.Entry[1].Key") from table; but that depends on having 'Business' always in the second position of the array.

I am going to suggest using something like SELECT JSON_EXTRACT(column,"$.PhysicalAddresses.Entry[*].Key") from table; which will return ["Home", "Business"] and then parsing in your application or working with JSON_CONTAINS()

answered Jan 23, 2019 at 17:11

Dave Stokes

8235 silver badges11 bronze badges

Collectives™ on Stack Overflow

Retrieving JSON array elements based on their values in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related