Postgresql – How to do this binary appearance analysis

jsonpostgresql

I have a table named results:

integer: id
json: websites

An example of websites column is:

[{"url": "http://google.com"}, {"url": "http://example.com"}]

What I want to do is generate a binary appearance chart, where it shows a 1 or a 0 depending on if ANY url in the JSON blob is contained within another set.

All the possibilities are on columns, and all the results are as rows.

result_id | wikipedia.org | google.com | example.com
10         1               1            1
11         0               1            0
12         0               0            0
13         1               0            0

For it to return 1, ANY url in the JSON array object (with key ->>{'url'}) must match the domain
The URLs are full urls so ideally I would like to use regex to check if its a match (i can't do a full string match because I only care about the HOST of the URL). I'm thinking something like /https?:\/\/(example\.com)(/|?|$)/

Best Answer

Assuming data type jsonb and current Postgres 10.

Exact matches are simpler, since you can use the built-in jsonb contains operator @>:

SELECT id
     , (websites @> '[{"url": "http://wikipedia.org"}]')::int AS "wikipedia.org"
     , (websites @> '[{"url": "http://google.com"}]'   )::int AS "google.com"
     , (websites @> '[{"url": "http://example.com"}]'  )::int AS "example.com"
FROM   results
ORDER  BY id;

The cast to integer only to meet your desired form of 1/0. Or omit the cast and just use the boolean t/f instead.

Pattern matching is more sophisticated, since there is no direct jsonb operator:

SELECT id
     , count(*) FILTER (WHERE url ~ '^https?://wikipedia\.org\M') AS "wikipedia.org"
     , count(*) FILTER (WHERE url ~ '^https?://google\.com\M')    AS "google.com"
     , count(*) FILTER (WHERE url ~ '^https?://example\.com\M')   AS "example.com"
FROM   results r
LEFT   JOIN LATERAL (
   SELECT elem->>'url' AS url
   FROM   jsonb_array_elements(r.websites) w(elem)
   ) w ON true
GROUP  BY id
ORDER  BY id;

db<>fiddle here

Replace count(*) FILTER ... with bool_or(url ~ '^https?://wikipedia\.org\M') etc. if URLs are never contained more than once and/or you are not interested in the actual count.

Postgresql – How to select bytea data as binary data, inserted as hex-encoded from binary

You're not going to get binary data out of psql and SELECT. psql will format it for readability. You could try something with lo_export similar to the answer in How to insert (file) data into a PostgreSQL bytea column to get the data written to a file.

However, most PostgreSQL client adapters (perl DBI, Ruby 'pg', etc) can give you the data in binary when you SELECT bytea columns. Just write a little program using an appropriate adapter in the language of your choice.

Postgresql – Returning case-sensitive input of case-insensitive array match

First of all, you should mention that you are using the additional module citext that provides the case insensitive text data type citext, which is not strictly needed here. The solution below only requires standard Postgres 9.4.

Key elements are jsonb_each_text() to unnest jsonb values into key/value pairs and json_object_agg() to re-assemble the JSON value with changed parts:

SELECT j.id, json_object_agg(d.key, CASE WHEN d.key = 'email'
                                         THEN t.email ELSE d.value END)::jsonb AS d
FROM   jsonthings j
JOIN   unnest('{TEST1@TEST.COM,test3@test.com}'::text[]) t(email)
                                                ON lower(t.email) = lower(j.d->>'email')
     , jsonb_each_text(j.d) d
GROUP  BY 1;

Result:

id | d
---+---------------------------------------
1  | {"id": "1", "email": "TEST1@TEST.COM"}
3  | {"id": "3", "email": "test3@test.com"}

How?

(0.) Of yourse your table has a primary key you did not mention. Let's call it id.

Unnest the text array into individual rows and JOIN to the 'email' value of the jsonb column using lower() on both sides to make it case insensitive.
Decompose the jsonb column (only identified rows!) into key/value pairs with jsonb_each_text().
Replace original 'email' values with the search string with a CASE statement.
Finally aggregate everything back to a json object with json_object_agg() and cast to jsonb.

Aside: For a simple structured form like you present in the example, a normalized schema with scalar Postgres data types would be much more efficient:

How to perform update operations on columns of type JSONB in Postgres 9.4

Best Answer

Related Solutions

Postgresql – How to select bytea data as binary data, inserted as hex-encoded from binary

Postgresql – Returning case-sensitive input of case-insensitive array match

How?

Related Question