How to do this binary appearance analysis
Assuming data type jsonb and current Postgres 10.

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

SELECT id
     , (websites @&gt; '[{"url": "http://wikipedia.org"}]')::int AS "wikipedia.org"
     , (websites @&gt; '[{"url": "http://google.com"}]'   )::int AS "google.com"
     , (websites @&gt; '[{"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-&gt;&gt;'url' AS url
   FROM   jsonb_array_elements(r.websites) w(elem)
   ) w ON true
GROUP  BY id
ORDER  BY id;


db&lt;>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.

Related:


Search for nested values in jsonb array with greater operator
Indexed range comparison of array key&#39;s value in row inside JSONB
Return counts for multiple ranges in a single SELECT statement
Collapsing a column to yes/no on query
What is the difference between LATERAL and a subquery in PostgreSQL?