I have a database table where one field (payload) is a string where a JSON-object is stored. This JSON has multiple attributes. I would like to find a way to query all entries where the payload json-object contains the same value for the attribute id_o to find dupplicates.

So for example if there existed multiple entries where id_o of the payload-string is "id_o: 100" I want to get these rows back. How can I do this?

Thanks in advance!

CodePudding user response：

I have faced similar issue like this before. I used regexp_substr

SELECT regexp_substr(yourJSONcolumn, '"id_o":"([^,]*)',0,1,'e')  end as   give_it_a_name

the comma in "([^,])" can be replaced with a "." if after the id_0:100 has a . or something else that you want to remove.

CodePudding user response：

i think storing json in database is not a good experience. Now your db needs a normalization, it will be good, if you create a new row in your db, give it a unique index and store this id_o property there.

UPDATE here what i find in another question:

If you really want to be able to add as many fields as you want with no limitation (other than an arbitrary document size limit), consider a NoSQL solution such as MongoDB.

For relational databases: use one column per value. Putting a JSON blob in a column makes it virtually impossible to query (and painfully slow when you actually find a query that works).

Relational databases take advantage of data types when indexing, and are intended to be implemented with a normalized structure.

As a side note: this isn't to say you should never store JSON in a relational database. If you're adding true metadata, or if your JSON is describing information that does not need to be queried and is only used for display, it may be overkill to create a separate column for all of the data points.

CodePudding user response：

I guess you JSON looks like this: {..,"id_o":"100",..}

SELECT * FROM your_table WHERE your_column LIKE '%"id_o":"100"%'