0

Hey I have a table with two columns - A and B. Column A is a VARCHAR. Column B has stringified list of jsons. The size of the list varies. Every JSON has a key called "foo". As a part of a query, I want to check if A = any of the foo values So somehow get all the "foo" values from every json in a list and then maybe use a WHERE IN condition

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Sid Anand
  • 167
  • 1
  • 10
  • Wich version of MySQL are you using? In any case, this seems to be a problem for the business datalayer, not the database – Leandro Bardelli Nov 28 '21 at 16:35
  • Does this answer your question? [How to search JSON data in MySQL?](https://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql) – Leandro Bardelli Nov 28 '21 at 16:36
  • 1
    It would help if you show a mock-up example of your JSON. It's not clear from your question how the JSON is structured. You say it has a key "foo" but you also mention "any of the foo values." Does that mean the "foo" key has an array value? This would be cleared up if you show an example. – Bill Karwin Nov 28 '21 at 18:07
  • Also please state what `SELECT VERSION();` returns, because the answer could depend on what exact database version you use. – Bill Karwin Nov 28 '21 at 18:07
  • Sorry, @Sid. I downvoted your question. Please, be more specific and provide sample data and the code you wrote so far. Ok, we are genius, we can make abstractions and decisions. But, man, it's not funny. 'Foo', 'A', 'B'. – Marcus Vinicius Pompeu Nov 28 '21 at 18:39

1 Answers1

0

You can use json_table in a subquery:

select t.a, exists (select 1 from json_table(t.b, '$[*]' columns (foo text path '$.foo')) t3 
where t3.foo = t.a) from tbl t

See fiddle here.

Ajax1234
  • 69,937
  • 8
  • 61
  • 102
  • Hey @Ajax1234 , this code snippet isnt working. Is the code inside columns correct syntax? – Sid Anand Nov 29 '21 at 21:23
  • @SidAnand What is the error/output issue that you have recieved? Also, please post a sample of your db data itself. – Ajax1234 Nov 29 '21 at 21:24
  • Hey @Ajax1234, I am using SQLite in nodejs (version 3.0.3). Here is the fiddle: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=f654b193dc1878b3fee8e0eb35f0e068 – Sid Anand Nov 29 '21 at 21:27
  • @SidAnand Your original question was tagged `mysql`, `json_table` is not compatible with `sqlite`. – Ajax1234 Nov 29 '21 at 21:28