0

I have the column name devices in one table. In this column, I have JSON Data like this for each row

{"os": "Apple iPhone", "mac_address": "58:7f:57:d7:32:f2", "manufacturer": "Apple"}

enter image description here

If I Want to check whether the mac_address is present in that cell that has this JSON data or not, which query should I make? ( IF it is not present then the whole cell will be NULL.)

This is the task.

retrieve id for each mac address from users table in rds db (if available). if not available, new row should be inserted and id created;

anakin skywalker
  • 23
  • 6
  • you can find the answer easily from google search like "https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql" – Ahmmed Mar 05 '21 at 05:53
  • In MySQL use `json_column->>"$.mac_address" IS NOT NULL` – Akina Mar 05 '21 at 06:05
  • I removed the conflicting DBMS tags. Please add only the tag for the database product you are really using. (When it comes to JSON support the concrete DBMS product matters) –  Mar 05 '21 at 06:26
  • In Postgres, use `json_column ? 'mac_address'` –  Mar 05 '21 at 06:26
  • @a_horse_with_no_name I am using Amazon RDS with Postgres – anakin skywalker Mar 05 '21 at 07:19

1 Answers1

0
SELECT id from users WHERE devices ->> 'mac_address' = '58:32f:22:d3:32:f2';

Did it this way and it worked.

anakin skywalker
  • 23
  • 6