logo
Tags down

shadow

How to get the value of one specific JSON key in MySQL


By : Sakina Mcdermott
Date : September 15 2020, 06:00 AM
Hope that helps Use JSON_EXTRACT. SELECT dataResponse->"$.test.test1"
code :


Share : facebook icon twitter icon

Mysql to JSON specific rows


By : user2286006
Date : March 29 2020, 07:55 AM
To fix this issue First modify your query to add a WHERE clause to filter down the results. Then once you have the results, you can turn it into JSON 1 of 2 ways.

Get row from mysql using specific value with regexp ( json string )


By : Sai Kiran
Date : March 29 2020, 07:55 AM
hop of those help? If you only have numbers in the fields, you can alter your regexp to only take values where the string you are looking for (here the '3') does not have another number immediately close to it :
code :
SELECT * FROM `pages` WHERE access REGEXP '([^"0-9])3([^"0-9])'    

Querying a specific JSON key value from mysql


By : Ollie Smith
Date : March 29 2020, 07:55 AM
Does that help You could use JSON_EXTRACT to find the values of activity and compare them:
code :
$sql = "SELECT * 
        FROM table
        WHERE JSON_EXTRACT(column,  '$[*].activity') LIKE '%".$term."%'";

Querying in multilevel JSON in MariaDB/MySQL for specific value


By : Sree
Date : March 29 2020, 07:55 AM
Does that help I tested the following with MySQL 8.0. It uses functions that seem to be present in MariaDB according to its documentation, but I don't have a MariaDB instance to test.
code :
SELECT * FROM mytable;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"1": {"text": "Item1", "value": 1, "isActive": 0, "textDisplay": ""}, "2": {"text": "Item2", "value": 2, "isActive": 1, "textDisplay": ""}, "3": {"text": "Item3", "value": 3, "isActive": 1, "textDisplay": ""}, "nextValue": 4} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(data, REPLACE(JSON_UNQUOTE(JSON_SEARCH(data, 'one', 'Item2')), '.text', '')), '$.value')) AS value FROM mytable;
+-------+
| value |
+-------+
| 2     |
+-------+
SELECT j.* FROM mytable2, 
JSON_TABLE(mytable2.data, '$[*]' 
  COLUMNS (
    rowid FOR ORDINALITY,
    `text` VARCHAR(20) PATH '$.text',
    textDisplay TEXT PATH '$.textDisplay',
    value INT PATH '$.value',
    isActive BOOL PATH '$.isActive'
  )
) AS j

+-------+-------+-------------+-------+----------+
| rowid | text  | textDisplay | value | isActive |
+-------+-------+-------------+-------+----------+
|     1 | Item1 |             |     1 |        0 |
|     2 | Item2 |             |     2 |        1 |
|     3 | Item3 |             |     3 |        1 |
+-------+-------+-------------+-------+----------+
select * from mytable2;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"text": "Item1", "value": 1, "isActive": 0, "textDisplay": ""}, {"text": "Item2", "value": 2, "isActive": 1, "textDisplay": ""}, {"text": "Item3", "value": 3, "isActive": 1, "textDisplay": ""}] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
CREATE TABLE Items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `text` VARCHAR(20), 
  textDisplay TEXT, 
  value INT, 
  isActive BOOL
);
INSERT INTO Items 
VALUES (1, 'Item1', '', 1, 0),
       (2, 'Item2', '', 2, 1),
       (3, 'Item3', '', 3, 1);
SELECT value FROM Items WHERE `text` = 'Item2';
+-------+
| value |
+-------+
|     2 |
+-------+

How can I update specific value from MySQL JSON column


By : user2730761
Date : March 29 2020, 07:55 AM
may help you . I'm trying to update a specific value in JSON array from a MySQL column by searching for specific ID, I want it to be dynamic without specifying array key. , Try (adjust everything you consider necessary):
code :
UPDATE `myTable`
  INNER JOIN JSON_TABLE(
      `myTable`.`data`,
      '$[*]'
      COLUMNS(
        `position` FOR ORDINALITY,
        `ID` INT PATH '$.ID'
      )
    ) `der`
SET `data` = JSON_REPLACE(
  `myTable`.`data`,
  CONCAT('$[', `der`.`position` - 1, '].OD'),
  '3/1'
)
WHERE `der`.`ID` = 63010092;
Related Posts Related Posts :
  • What is the right version of MySQL Workbench to connect to a mysql 5.6 db?
  • How can I query in Japanese on Rails active record?
  • Mixing variable assigment and data retrieval - MySQL vs MS SQL Server
  • MYSQL ENUM ; Invalid Syntax
  • How to make a common auto increment id for two tables
  • Best database table design for a table with dependent column values
  • Is it advisable to add additional Indices to MySQL tables when using CakePHP
  • MySQL: Populating A Table with Random Dates or Null Vallues
  • How does MySql sort data when the data of the sort filed is duplicate?
  • WHAT DOES THIS MEAN GUYS?
  • MySQL: Returning Counts (*) with NULL or 0 Vales
  • How to Know records updated with Update query?
  • Failing to get the desired results for a RIGHT JOIN
  • Mysql issue when creating a new user
  • How to use a single table for multiple users
  • Update Email Matching Between 2 Tables in MySQL - BLACKLIST
  • Is it possible to add an integer holding months to a date type in a query and output with AS to have a new column in MYS
  • MySQL index is not used when certain columns are returned
  • Group by 200 rows, start with newest ID
  • Force liquibase to current_timestamp instead of now()
  • get the sum of datas in the same date and get an array for weekly reports
  • SQL Query Group by column values, and get o/p as separate column
  • Can't change Laravel table column type
  • How to store many to many relationship with extra column data
  • Is there a way to join databases of two different data sources(ie. Mysql and Postgres SQL) using logstash and indexing i
  • how can I rearrange the ID orders?
  • Unpivot table in MySQL
  • How to save data from the serial port to the mysql database with C programming
  • Insert into *table name* select *function call* doesn't work
  • Unable to connect to MySQL database from vps using localhost
  • How to make a SQL query to select rows with dynamic interval
  • How kubernetes service work in case of Database (mysql)?
  • Laravel Eloquent - Multiple NOT LIKE queries?
  • MySQL : How to aggregate table column into a list and count?
  • a sum() function with aritmatich 4 table
  • Deleting query in a SQL query without using types
  • MYSQL Join tables with a dynamic condition CASE
  • how to cleanly stop Mysql recover process
  • Why does the MySQL query fail within this Lua script?
  • Is there a way to not show the separator in CONCAT_WS when trying to concatinate empty or NULL row's?
  • Error 1064:You have an error in your SQL syntax - mysql select query
  • I need to find out the name and customer id of customers who bought 2 cars from all the 4 dealerships
  • Total products ordered by each customer
  • MySQL. search data in serialized column
  • RMySQL - automating reading data from DB tables
  • Mysql not like and able to find a record
  • An SQL query to get the exact matching results
  • How to properly use Knex / Bookshelf with MySQL on RDS
  • MySQL get dates from a range for which the records does not exist in the database table
  • Why mysql and perl base64 string encoding is different?
  • Second SELECT query in another table if first SELECT returns 0 rows
  • Mysql instance in google cloud console (Appengine) crashed
  • [Perl]read text file with delimiter and insert into mysql table in perl 5.10
  • Join syntax on MYSQL
  • mysql - merge cols into one row
  • SQL: return true/false if multiple values exists or not in a subquery
  • SQL-how to select only doubles/date/timeStamps/time/strings values from a string column?
  • SQL limit query optimization with condition and sort
  • Can you run a raw MySQL query in Django without a model?
  • Huge wp_post table
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk