PostgreSQL Search in Json format values

Question

I have a table named property data where in it contains a JSON column like this one

Example JSON per row

["5236", "5826.0", "400", "Concrete", "0", "Test 12", "Test 12", "", "56789.12384", "77878663.100239", "500", "Sample 1"]
["4427", "4606.0", "400", "Concrete", "0", "Test10", "SAMP", "", "1123.44567", "12394.11235", "01", "SAMP"]

what I want to do is to have a query that can search inside the given data. for example I only want to display those data that has Test 10. I tried using the following query but receiving an error

SELECT * FROM property_data WHERE json_data LIKE '%Test 10%';

Anyone has an idea to search inside the JSON column? Thank you.

I updated the answer with the sample data per row

AmyllaVimiar
– AmyllaVimiar

2020-02-06 22:13:09 +00:00
Commented Feb 6, 2020 at 22:13 — AmyllaVimiar
– AmyllaVimiar, Commented Feb 6, 2020 at 22:13

Henry · Accepted Answer · 2020-02-06 22:35:21Z

2

Method 1

If you only want to search a single array position (in this case index 5), you can use the ->> operator ("Get JSON array element as text") (documentation) to get the value in the position you're after and compare it (using LIKE) against your target value ("Test 10").

Example:

WHERE json_data->>5 LIKE '%Test 10%';

In context (the CTE is just for example purposes):

WITH property_data AS (
    SELECT
        '["5236", "5826.0", "400", "Concrete", "0", "Test 12", "T..."]'::json AS json_data
    UNION ALL
    SELECT
        '["4427", "4604.0", "400", "Concrete", "0", "Test11", "TP..."]'::json AS json_data
    UNION ALL
    SELECT
        '["4421", "4595.0", "400", "Concrete", "0", "Test 10", "T..."]'::json AS json_data
)

SELECT
    *
FROM
    property_data
WHERE
    json_data->>5 LIKE '%Test 10%';

With result:

                           json_data                           
---------------------------------------------------------------
 ["4421", "4595.0", "400", "Concrete", "0", "Test 10", "T..."]
(1 row)

Method 2

Alternatively, if you just want to search the whole field, you can cast the column from json to text and then use LIKE as before.

Example:

WITH property_data AS (
    SELECT
        '["5236", "5826.0", "400", "Concrete", "0", "Test 12", "T..."]'::json AS json_data
    UNION ALL
    SELECT
        '["4427", "4604.0", "400", "Concrete", "0", "Test11", "TP..."]'::json AS json_data
    UNION ALL
    SELECT
        '["4421", "4595.0", "400", "Concrete", "0", "Test 10", "T..."]'::json AS json_data
)

SELECT
    *
FROM
    property_data
WHERE
    json_data::text LIKE '%Test 10%';

With the same result as above (at least for this example).

edited Feb 6, 2020 at 22:35

user330315

answered Feb 6, 2020 at 22:14

Henry

15.9k7 gold badges51 silver badges69 bronze badges

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

6 Comments

AmyllaVimiar Over a year ago

Thank you for the answer. it works when searching string like test 10. but how about when searching numbers?

Henry Over a year ago

@AmyllaVimiar what do you mean searching numbers exactly? Like checking if a certain number is in the json_data column?

AmyllaVimiar Over a year ago

Yes, like if there's 400 in a given record. I figured it out I didn't changed the value.Thank you for the answer!

AmyllaVimiar Over a year ago

just have other question. how about searching on the json without defining the index where I want to search for it? because I'm searching dynamically with different values for example there are time wherein i can search for both Test 10, Test 1.

AmyllaVimiar Over a year ago

Thank you so much for your help

|

Collectives™ on Stack Overflow

PostgreSQL Search in Json format values

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related