NodeJS, MySQL - JSON Stringify - Advanced query

Question

I have an object in the table column saved using JSON.stringify and it looks like this:

[{
    "id": 1,
    "uID": 10
}, {
    "id": 2,
    "uID": 10
}, {
    "id": 3,
    "uID": 94
}]

I need a query that will check if a given column contains values e.g.

I want uID = 10 and id = 2 will return
I want uID = 10 and id = 5 will not return it
I want uID = 10 and id = 2, uID = 94 and id = 0 will not return it (because uID = 94 and id = 0 is not here)

What have you tried so far? Where are you stuck?

Nico Haase
– Nico Haase

2019-11-19 15:35:00 +00:00
Commented Nov 19, 2019 at 15:35 — Nico Haase
– Nico Haase, Commented Nov 19, 2019 at 15:35

Owain Esau · Accepted Answer · 2019-11-28 04:36:42Z

1

Unless you are querying programmatically where you can parse the JSON and then do the logic, I would recommend something like this:

SELECT * FROM Table WHERE Column LIKE '%"id": 1,"uID": 10%'

The LIKE keyword allows us to use wildcards (%) but still do an exact text match for what we define.

edited Nov 28, 2019 at 4:36

Owain Esau

1,9222 gold badges23 silver badges37 bronze badges

answered Nov 18, 2019 at 23:04

Michaela D

464 bronze badges

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

Comments

Michaela D · Accepted Answer · 2019-11-19 15:50:54Z

0

Add a wildcard in the middle, but note that order matters with this strategy.

SELECT * FROM Table WHERE Column LIKE '%"id": 1%"id": 2%'

answered Nov 19, 2019 at 15:50

Michaela D

464 bronze badges

Comments

Tabam · Accepted Answer · 2019-11-22 18:57:54Z

0

I need it to work backwards too:] e.g. I have

[{
    "id": 1,
    "uID": 10
}, {
    "id": 2,
    "uID": 55
}, {
    "id": 3,
    "uID": 94
}]

SELECT * FROM Table WHERE Column LIKE '%"uID": 55%"uID": 94%' <- will be working

SELECT * FROM Table WHERE Column LIKE '%"uID": 94%"uID": 55%' <- will be not working

Does not work "back"

edited Nov 22, 2019 at 18:57

answered Nov 19, 2019 at 16:36

Tabam

1131 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

NodeJS, MySQL - JSON Stringify - Advanced query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related