Скачать или смотреть Searching JSON Data in Laravel 6.x Using Eloquent

Discover how to effectively search JSON column values in Laravel 6.x using Eloquent. Streamline your data retrieval process with simple code examples and explanations.
---
This video is based on the question https://stackoverflow.com/q/71068147/ asked by the user 'Zabs' ( https://stackoverflow.com/u/218725/ ) and on the answer https://stackoverflow.com/a/71068472/ provided by the user 'Chin Leung' ( https://stackoverflow.com/u/5556440/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Using Laravel 6.x eloquent (or similar method) to get row by JSON column data

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Searching JSON Data in Laravel 6.x Using Eloquent: A Step-by-Step Guide

In modern web application development, dealing with complex data formats often becomes a significant challenge. Laravel, a popular PHP framework, offers powerful tools to manage and query data with ease. One such challenge you may encounter is searching through JSON columns stored in your database. This article aims to give you an in-depth understanding of how to perform such searches using Laravel 6.x Eloquent ORM.

The Problem: Searching JSON Column Data

Imagine you have a table named workflow_conflicts with a column called conflicts that contains JSON data. An example output from this model might look like this:

[[See Video to Reveal this Text or Code Snippet]]

You would like to retrieve records from the workflow_conflicts table using values from the conflicts JSON column, particularly the values of text and word. For instance, your initial attempt might resemble this:

[[See Video to Reveal this Text or Code Snippet]]

Despite this working perfectly, you face difficulty when trying the following search:

[[See Video to Reveal this Text or Code Snippet]]

Unfortunately, you receive a null result, which indicates that this syntax isn't effective for querying JSON data in Laravel.

The Solution: Using whereRaw with JSON Functions

The answer lies in utilizing the raw database querying abilities of Laravel's Eloquent. Specifically, you can use whereRaw combined with the JSON function provided by MySQL. Here's how you can perform the search within your JSON column:

Step-by-Step Guide

Use whereRaw Method: Instead of assuming that Laravel can directly parse the JSON structure with the -> operator, leverage the power of the raw SQL functions available in MySQL.

Construct the Query: Here’s the correct way to construct your query to search through the conflicts JSON data:

[[See Video to Reveal this Text or Code Snippet]]

Understand the Query:

json_contains() is a MySQL function that checks if JSON data contains a specified value.

json_object("text", "validatorword 1218") constructs a JSON object with the structure to search for the text key specifically holding the value of validatorword 1218.

Example Implementation

With the above knowledge, here's the complete example in a typical controller or Tinker command:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

Searching within JSON columns in Laravel requires a nuanced understanding of both Eloquent's capabilities and the underlying SQL functions provided by your database. By employing whereRaw along with JSON functions such as json_contains, you can efficiently query JSON data, enhancing your application's performance and reliability.

Armed with this knowledge, you can now easily retrieve records based on JSON data stored in your Laravel application. Happy coding!