<jupyter_start><jupyter_text>OpenSearch OSDD AnalysisNote: there's a number for identified OpenSearch responses that's roughly 5,500 - this includes opensearch result sets. The 4K number is OpenSearch OSDDs.4636 == number of OSDDs.Data processing can be found in the [OpenSearch OSDD Evaluation](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/data_processing/OpenSearch%20OSDD%20Evaluation.ipynb).<jupyter_code>%matplotlib inline
import pandas as pd
import json as js  # name conflict with sqla
import sqlalchemy as sqla
from sqlalchemy.orm import sessionmaker

pd.set_option("display.max_rows",250)
# grab the clean text from the rds
with open('../local/big_rds.conf', 'r') as f:
    conf = js.loads(f.read())

# our connection
engine = sqla.create_engine(conf.get('connection'))<jupyter_output><empty_output><jupyter_text>So what do we see in the 4636 responses?<jupyter_code>sql = """select kvps.value, count(kvps.value)
from osdds,
    json_each_text(namespaces) as kvps
group by kvps.value
order by count desc, kvps.value
;"""
df = pd.read_sql(sql, engine)
df
# number of references to a namespace<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-namespaces.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_namespaces.csv)<jupyter_code># which ones contain both time and geo?

sql = """
select url, 
    case when namespaces::jsonb ? 'geo' then True else False end as contains_geo,
    case when namespaces::jsonb ? 'time' then True else False end as contains_time
from osdds
where namespaces::jsonb ?| array['time', 'geo'];
"""
df = pd.read_sql(sql, engine)
from IPython.display import display
with pd.option_context('display.max_colwidth', 1000):
    display(df)<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-time_geo_namespaces.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_time_geo_namespaces.csv)Only 32 of those contain both the time and geo namespaces (this is awkward, going by prefixes in the postgres json).The SQL for that count:```with a as (	select url, 		case when namespaces::jsonb ? 'geo' then True else False end as contains_geo,		case when namespaces::jsonb ? 'time' then True else False end as contains_time	from osdds	where namespaces::jsonb ?| array['time', 'geo'])select count(*)from a where contains_geo = True and contains_time = True;```And, it looks like most of those are related to an ESIP participant (ie someone who knows Doug). But there are dataset-level searches in the above set so let's count by host.<jupyter_code>sql = """
with a as (
    select url, response_id, 
        case when namespaces::jsonb ? 'geo' then True else False end as contains_geo,
        case when namespaces::jsonb ? 'time' then True else False end as contains_time
    from osdds
    where namespaces::jsonb ?| array['time', 'geo']
)

select r.host, count(*)
from a join responses r on r.id = a.response_id
where contains_geo = True and contains_time = True
group by r.host;
"""
df = pd.read_sql(sql, engine)
df<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-time_geo_namespaces_by_host.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_time_geo_namespaces_by_host.csv) So large federal data centers and a schema. The parameter and facet extensions are not well-represented either.<jupyter_code># do they contain any descriptive text?
sql = """
select o.id, o.has_title, o.has_description, o.has_keywords
from osdds o
where status_code < 400 and status_code is not null;
"""
df = pd.read_sql(sql, engine)
df[:10]<jupyter_output><empty_output><jupyter_text>maybe. let's count3,983 have a title.3,650 have a description.561 have a set of keywords.560 have all three.(Note: we do not have token counts here. Not explicitly for these elements - the token count for the complete OSDD is available.) Linkrot on the harvested URLS<jupyter_code>sql = """
select o.status_code, count(o.id), 
    -- add the duration between checked and harvested
    avg(extract(day from o.date_verified - r.initial_harvest_date))::int as avg_lifespan_in_days,
    min(extract(day from o.date_verified - r.initial_harvest_date))::int as min_lifespan_in_days,
    max(extract(day from o.date_verified - r.initial_harvest_date))::int as max_lifespan_in_days
from osdds o join responses r on r.id = o.response_id
group by o.status_code
order by o.status_code ASC;
"""
df = pd.read_sql(sql, engine)
df

# this makes no sense as a table. lifespan is harvest to linkrot check
# NaN as status_code is related to requests exceptions, no code.
df.sort('avg_lifespan_in_days')

# meh, still not a good look
from IPython.display import Image
Image(filename='screenshots/osdd_linkrot_area.png') 

# this is not a good chart<jupyter_output><empty_output><jupyter_text>| Date       | 200 | 300 | 400 | 500 | 999 | |------------|-----|-----|-----|-----|-----| | 7/17/2014  | 41  | 2   | 2   |     | 5   | | 7/29/2014  | 21  | 3   | 3   |     |     | | 7/30/2014  | 30  | 2   | 3   |     | 1   | | 7/31/2014  | 12  | 2   |     |     | 3   | | 10/27/2014 | 5   | 13  | 1   | 1   |     | | 10/28/2014 | 102 | 47  | 8   | 1   | 2   | | 10/29/2014 | 58  | 5   | 7   |     | 4   | | 10/30/2014 | 332 | 39  | 25  | 24  | 40  | | 3/4/2015   | 9   | 1   |     |     | 1   | | 3/6/2015   | 6   |     |     |     |     | | 3/12/2015  | 54  | 11  | 33  | 6   | 12  | | 3/13/2015  | 11  | 2   | 5   | 1   |     | | 3/15/2015  | 3   |     | 10  |     | 1   | | 3/18/2015  | 67  | 2   | 1   |     |     | | 3/19/2015  | 37  | 7   | 2   | 1   | 3   | | 3/20/2015  | 43  | 7   | 3   | 20  | 3   | | 6/16/2015  | 28  | 1   | 1   |     | 3   | | 6/18/2015  | 23  |     |     | 1   | 2   | | 6/19/2015  | 2   |     |     |     |     | | 6/20/2015  | 5   | 1   |     |     |     | | 6/22/2015  | 4   | 1   |     |     |     | | 6/23/2015  | 17  | 1   | 1   | 2   | 1   | | 6/24/2015  | 69  |     |     | 11  | 1   | | 6/25/2015  | 103 | 3   |     | 14  | 2   | | 6/26/2015  | 95  | 1   | 2   | 10  | 7   | | 6/27/2015  | 109 | 2   |     | 17  | 1   | | 6/28/2015  | 74  | 2   |     | 9   | 3   | | 6/29/2015  | 9   |     |     | 3   | 1   | | 7/1/2015   | 16  | 1   | 1   | 3   |     | | 7/2/2015   | 50  |     |     | 13  | 2   | | 7/3/2015   | 40  | 3   | 1   | 8   | 2   | | 7/4/2015   | 83  |     |     | 12  | 3   | | 7/5/2015   | 112 |     |     | 22  | 6   | | 7/6/2015   | 50  |     | 1   | 14  | 1   | | 7/7/2015   | 3   |     |     |     |     | | 7/8/2015   | 24  | 1   | 3   |     | 1   | | 7/9/2015   | 44  | 3   | 1   | 2   | 4   | | 7/10/2015  | 101 | 1   |     | 17  | 5   | | 7/11/2015  | 83  | 2   |     | 14  | 2   | | 7/12/2015  | 141 | 1   | 2   | 9   | 3   | | 7/13/2015  | 77  | 2   | 2   | 9   | 3   | | 7/14/2015  | 33  | 1   |     | 2   | 1   | | 7/15/2015  | 32  |     |     | 2   |     | | 7/16/2015  | 56  |     | 1   | 5   |     | | 7/17/2015  | 13  | 1   | 1   |     | 3   | | 7/18/2015  | 8   | 1   |     |     | 1   | | 7/19/2015  | 5   |     |     |     |     | | 7/21/2015  | 3   |     |     | 1   | 1   | | 7/22/2015  | 91  | 1   |     |     | 1   | | 7/28/2015  | 12  |     |     |     | 1   | | 7/29/2015  | 24  | 1   |     |     | 1   | | 7/30/2015  | 6   |     |     |     | 1   | | 7/31/2015  | 19  | 1   | 1   |     | 2   | | 8/1/2015   | 11  |     | 1   |     |     | | 8/2/2015   | 19  | 1   | 1   |     |     | | 8/3/2015   | 9   |     |     |     | 2   | | 8/4/2015   | 158 | 3   |     | 5   | 7   | | 8/5/2015   | 103 |     | 3   | 1   | 2   | | 8/6/2015   | 14  |     |     | 1   |     | | 8/7/2015   | 42  | 3   |     |     | 1   | | 8/8/2015   | 30  | 1   |     |     | 5   | | 8/9/2015   | 21  | 2   |     |     |     | | 8/10/2015  | 60  |     |     |     | 2   | | 8/11/2015  | 90  | 1   |     |     | 2   | | 8/12/2015  | 121 | 1   | 1   |     | 2   | | 8/13/2015  | 70  | 1   | 1   | 1   | 2   | | 8/14/2015  | 34  |     |     | 1   |     | | 9/2/2015   | 37  | 2   |     | 1   | 10  | | 9/3/2015   | 23  |     |     |     | 1   | | 9/4/2015   | 17  |     |     | 2   |     | | 9/6/2015   | 24  |     |     | 1   | 1   | | 9/7/2015   | 53  | 2   |     | 6   | 10  | | 9/8/2015   | 45  | 1   | 1   | 1   | 6   | | 9/9/2015   | 21  | 2   |     |     | 3   | | 9/10/2015  | 133 | 2   |     |     | 11  | | 9/11/2015  | 33  | 2   |     |     | 2   | | 9/12/2015  | 39  |     |     |     |     | | 9/13/2015  | 8   |     |     |     |     | | 9/14/2015  | 30  | 2   |     |     |     | | 9/15/2015  | 17  |     | 1   |     |     | | 9/16/2015  | 14  |     |     |     |     | | 9/17/2015  | 8   |     |     |     | 1   | | 9/18/2015  | 3   |     |     |     | 2   | | 9/19/2015  | 2   |     |     |     |     | | 9/20/2015  | 4   |     |     |     |     | | 9/22/2015  | 1   |     |     |     | 1   | | 9/23/2015  | 1   |     |     |     |     | and the unpivoted source sql:```with a as (	select o.id, case when o.status_code is null then 999 else o.status_code end as status	from osdds o)select a.status, count(a.status), r.initial_harvest_date::datefrom osdds o join responses r on r.id = o.response_id	join a on a.id = o.idgroup by a.status, r.initial_harvest_date::dateorder by r.initial_harvest_date::date ASC;```<jupyter_code>sql = """
with a as (
  select o.id, round(case when o.status_code is null then 900 else o.status_code end, -2) as status
  from osdds o
)
select a.status, count(a.status) as num, date_trunc('month', r.initial_harvest_date)::date as months
from osdds o join responses r on r.id = o.response_id
  join a on a.id = o.id
group by a.status, months
order by months ASC;
"""

df = pd.read_sql(sql, engine)
df.pivot(index='months', columns='status', values='num')<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_linkrot_by_harvest.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_linkrot_by_harvest.csv)As percents| Month      | 200   | 300   | 400   | 500   | 900  | |------------|-------|-------|-------|-------|------| | 2014-07-01 | 80.00 | 6.92  | 6.15  | 0.00  | 6.92 | | 2014-10-01 | 69.61 | 14.57 | 5.74  | 3.64  | 6.44 | | 2015-03-01 | 63.54 | 8.29  | 14.92 | 7.73  | 5.52 | | 2015-06-01 | 83.80 | 1.87  | 0.62  | 10.44 | 3.27 | | 2015-07-01 | 84.22 | 1.50  | 1.05  | 9.95  | 3.29 | | 2015-08-01 | 93.54 | 1.56  | 0.84  | 1.08  | 2.99 | | 2015-09-01 | 87.39 | 2.21  | 0.34  | 1.87  | 8.18 |<jupyter_code>df.pivot(index='months', columns='status', values='num').plot(kind='bar', figsize=(16, 6))
from IPython.display import Image
Image(filename='screenshots/opensearch_linkrot.png')<jupyter_output><empty_output><jupyter_text>Onwards to the Templates!So does it have a template? Are they for ATOM and/or RSS? Can you build a search URL from them and get a successful result?<jupyter_code>sql = """
with a as (
    select id, 
        case when url_templates is not null and url_templates::text != '[]'::text then True else False end as has_templates
    from osdds
)
select has_templates, cnt, round(100.0*cnt/(sum(cnt) OVER ()), 2) as pct
from (select has_templates, count(*) as cnt from a group by has_templates) foo;
"""
df = pd.read_sql(sql, engine)
df<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_templates.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_templates.csv)Not so many have templates.(I generated these urls, see [other notebook] but there can be issues in that.)How many havedefault_urls? 577search_urls? 559  (95%)example_urls? 299 (51%)For search urls, it means I could identify the searchTerms key. For example urls, those are built using the search url with the first item in the "example" element for that format.It is tracking like the OSDD, at the very least, isn't meant to be public for most of these sites. OpenSearch for the site search under the hood but nothing visible (CMS' are doing this for RSS feeds - it's in the html but not displayed.) So that makes sense for not bothering with templates or examples - they're not meant to *be* publicly available services.<jupyter_code>sql = """
with t 
as (
    select o.id, x.*
    from osdds o, 
        jsonb_to_recordset(o.url_templates::jsonb) 
            as x(
                default_url text,
                search_url text,
                example_url text,
                responses jsonb,
                parameters jsonb,
                param_defs jsonb,
                accept_type text
            )
    where url_templates is not null and url_templates::text != '[]'::text
)

select t.accept_type, count(t.id)
from t
group by accept_type;
"""

df = pd.read_sql(sql, engine)
df

# accept_type here is the mimetype from the "type" tag of the template<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_templates_by_mimetype.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_templates_by_mimetype.csv)Only 6 OSDDs included parameter definitions as a Parameter element.How many parameters, defined in the template strings, include something to mark them as Dataset searches?(spoiler, this isn't how cwic manages their own.)<jupyter_code>sql = """
with t 
as (
    select o.id, x.*
    from osdds o, 
        jsonb_to_recordset(o.url_templates::jsonb) 
            as x(
                default_url text,
                search_url text,
                example_url text,
                responses jsonb,
                parameters jsonb,
                param_defs jsonb,
                accept_type text
            )
    where url_templates is not null and url_templates::text != '[]'::text
)

select distinct jsonb_object_keys(parameters) as key
from t
order by key;
"""
pd.read_sql(sql, engine)<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_dataset_params.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_dataset_params.csv)<jupyter_code>sql = """
with t 
as (
    select o.id, x.*
    from osdds o, 
        jsonb_to_recordset(o.url_templates::jsonb) 
            as x(
                default_url text,
                search_url text,
                example_url text,
                responses jsonb,
                parameters jsonb,
                param_defs jsonb,
                accept_type text
            )
    where url_templates is not null and url_templates::text != '[]'::text
)

select distinct kvps.value
from t, jsonb_each_text(parameters) as kvps
order by kvps.value;
"""

pd.read_sql(sql, engine)<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_dataset_params_full.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_dataset_params_full.csv)For the results of the secondary requests.Starting with the search url (base url + default parameters + searchTerms without a term)<jupyter_code>sql = """
with t 
as (
	select o.id, x.*
	from osdds o, 
		jsonb_to_recordset(o.url_templates::jsonb) 
			as x(
				default_url text,
				search_url text,
				example_url text,
				responses jsonb,
				accept_type text
			)
	where url_templates is not null and url_templates::text != '[]'::text
)

select distinct t.search_url, t.accept_type, search_response->'status' as status_code, 
	search_response->'total' as total, 
	search_response->'subset' as subset, 
	search_response->'has_content' as has_content, 
	search_response->'error' as error,
	case when search_response->>'search_rels' != '{}' then True else False end as has_nested_search_urls
from t,
	jsonb_to_recordset(responses) as y(
		search_response jsonb,
		example_response jsonb,
		default_response jsonb
	)
where search_response->>'status' != '{}'
;

"""

df = pd.read_sql(sql, engine)

from IPython.display import display
with pd.option_context('display.max_colwidth', 1000, 'display.max_rows', 810):
    display(df)<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_search_urls.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_search_urls.csv)<jupyter_code># how many of the search urls (and there can be more than one per OSDD!)
# return a 200 OK?

sql = """
with t 
as (
	select o.id, x.*
	from osdds o, 
		jsonb_to_recordset(o.url_templates::jsonb) 
			as x(
				default_url text,
				search_url text,
				example_url text,
				responses jsonb,
				accept_type text
			)
	where url_templates is not null and url_templates::text != '[]'::text
)

select round((search_response->'status')::text::int, -2) as code,
    t.accept_type,
	count(distinct t.search_url) as num
from t,
	jsonb_to_recordset(responses) as y(
		search_response jsonb,
		example_response jsonb,
		default_response jsonb
	)
where search_response->>'status' != '{}'
group by code, t.accept_type
order by code asc, t.accept_type
;
"""
df = pd.read_sql(sql, engine)
df

# out of 559 responses with search urls<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_search_url_linkrot.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_search_url_linkrot.csv)<jupyter_code># by actual response count (above is the urls, can be more than one per response)
sql = """
with t 
as (
	select o.id, x.*
	from osdds o, 
		jsonb_to_recordset(o.url_templates::jsonb) 
			as x(
				search_url text,
				example_url text,
				responses jsonb,
				accept_type text
			)
	where url_templates is not null and url_templates::text != '[]'::text
)
select count(distinct t.id) as responses
from t,
	jsonb_to_recordset(responses) as y(
		search_response jsonb,
		example_response jsonb,
		default_response jsonb
	)
where search_response->>'status' != '{}'
	and round((search_response->'status')::text::int, -2) = 200
;
"""
df = pd.read_sql(sql, engine)
df

# so about 88% have a good search url<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_search_url_linkrot_by_response.sql)<jupyter_code># for the example urls

sql = """
with t 
as (
	select o.id, x.*
	from osdds o, 
		jsonb_to_recordset(o.url_templates::jsonb) 
			as x(
				default_url text,
				search_url text,
				example_url text,
				responses jsonb,
				accept_type text
			)
	where url_templates is not null and url_templates::text != '[]'::text
)

select round((example_response->'status')::text::int, -2) as code,
	t.accept_type,
	count(distinct t.example_url) as num
from t,
	jsonb_to_recordset(responses) as y(
		search_response jsonb,
		example_response jsonb,
		default_response jsonb
	)
where example_response->>'status' != '{}'
group by code, t.accept_type
order by code asc, t.accept_type
;

"""

df = pd.read_sql(sql, engine)
df

# out of 299 responses with example urls<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_example_urls.sql)<jupyter_code># by actual response count (above is the urls, can be more than one per response)
sql = """
with t 
as (
	select o.id, x.*
	from osdds o, 
		jsonb_to_recordset(o.url_templates::jsonb) 
			as x(
				search_url text,
				example_url text,
				responses jsonb,
				accept_type text
			)
	where url_templates is not null and url_templates::text != '[]'::text
)
select count(distinct t.id) as responses
from t,
	jsonb_to_recordset(responses) as y(
		search_response jsonb,
		example_response jsonb,
		default_response jsonb
	)
where example_response->>'status' != '{}'
	and round((example_response->'status')::text::int, -2) = 200
;
"""
df = pd.read_sql(sql, engine)
df

# so about 92% have a good search url<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_example_url_linkrot_by_response.sql)<jupyter_code># and the default urls
sql = """
with t 
as (
	select o.id, x.*
	from osdds o, 
		jsonb_to_recordset(o.url_templates::jsonb) 
			as x(
				default_url text,
				search_url text,
				example_url text,
				responses jsonb,
				accept_type text
			)
	where url_templates is not null and url_templates::text != '[]'::text
)

select round((default_response->'status')::text::int, -2) as code,
	t.accept_type,
	count(distinct t.default_url) as num
from t,
	jsonb_to_recordset(responses) as y(
		search_response jsonb,
		example_response jsonb,
		default_response jsonb
	)
where default_response->>'status' != '{}'
group by code, t.accept_type
order by code asc, t.accept_type
;
"""
df = pd.read_sql(sql, engine)
df<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_default_urls.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_default_urls.csv)<jupyter_code># for the search urls, what do we find in the results?
# a quick round of good does it include pagination info?
# additional OSDD links?

sql = """
with t 
as (
	select o.id, x.*
	from osdds o, 
		jsonb_to_recordset(o.url_templates::jsonb) 
			as x(
				default_url text,
				search_url text,
				example_url text,
				responses jsonb,
				accept_type text
			)
	where url_templates is not null and url_templates::text != '[]'::text
)

select distinct t.search_url, t.accept_type, search_response->'status' as status_code, 
	search_response->'total' as total, 
	search_response->'subset' as subset, 
	search_response->'has_content' as has_content, 
	search_response->'error' as error,
	case when search_response->>'search_rels' != '{}' then True else False end as has_nested_search_urls
from t,
	jsonb_to_recordset(responses) as y(
		search_response jsonb
	)
where search_response->>'status' != '{}' and 
	(search_response->'total')::text != '' and (search_response->'total')::text != '"Unknown"'
	and (search_response->'total')::text != '"0"'
;
"""

# for example<jupyter_output><empty_output><jupyter_text>For the search set, of 760 requests (atom or rss), only 61 returned a result set, 181 returned an empty resultset. 406 return a total count of "Unknown". 547 of the responses contain some reference to another search URL. This does not necessarily indicate a nested search structure, ie the dataset->granule concept. From the processing, this is a link in the search results with the "application/opensearchdescription+xml" type. There's no directionality attribute to these link elements so it could be the original OSDD reference or a nested search link.<jupyter_code># for the search responses, do they contain an OSDD 
# search link and does that link match the original OSDD url?
# we don't expect it to match the search url, that would be tagged
# as "self" in the link rel.


# the set where the source url doesn't match the resultset search osdd.
sql = """
with t 
as (
	select o.id, o.url, x.*
	from osdds o, 
		jsonb_to_recordset(o.url_templates::jsonb) 
			as x(
				default_url text,
				search_url text,
				example_url text,
				responses jsonb,
				accept_type text
			)
	where url_templates is not null and url_templates::text != '[]'::text
)

select distinct t.search_url, t.accept_type, 
    t.url,
	trim(both '"' from (v.value->'link_url')::text) as search_rel_osdd
from t,
	jsonb_to_recordset(responses) as y(
		search_response jsonb
	), 
	jsonb_array_elements(search_response->'search_rels') v
where search_response->>'status' != '{}' and search_response->>'search_rels' != '{}'
	and position(trim(both '"' from (v.value->'link_url')::text) in t.url) < 1
;
"""

df = pd.read_sql(sql, engine)
df<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/opensearch/select-osdd_search_url_matches_original.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-osdd_search_url_matches_original.csv)<jupyter_code># that's a little hard to see
# but 200 of the 700+ search urls have what might
# be a nested url option
for t in df.itertuples():
    print t[1]
    print '\tsrc: ', t[3]
    print '\tset: ', t[4]
# how many aren't wordpress links then?

sql = """
with t 
as (
	select o.id, o.url, x.*
	from osdds o, 
		jsonb_to_recordset(o.url_templates::jsonb) 
			as x(
				default_url text,
				search_url text,
				example_url text,
				responses jsonb,
				accept_type text
			)
	where url_templates is not null and url_templates::text != '[]'::text
)

select distinct t.search_url, t.accept_type, 
    t.url,
	trim(both '"' from (v.value->'link_url')::text) as search_rel_osdd
from t,
	jsonb_to_recordset(responses) as y(
		search_response jsonb
	), 
	jsonb_array_elements(search_response->'search_rels') v
where search_response->>'status' != '{}' and search_response->>'search_rels' != '{}'
	and position(trim(both '"' from (v.value->'link_url')::text) in t.url) < 1
    and trim(both '"' from (v.value->'link_url')::text) != 'https://wordpress.com/opensearch.xml'
;
"""

df = pd.read_sql(sql, engine)
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13211057947900054, 'fraction_numerical': 0.048986101617680564, 'mean_word_length': 3.004714111922141, 'pattern_counts': {'":': 0, '<': 88, '<?xml version=': 0, '>': 137, 'https://': 29, 'lorem ipsum': 0, 'www.': 0, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 74, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '355852', 'license': 'MIT', 'n_tokens_mistral': 10825, 'n_tokens_neox': 9458, 'n_words': 2360, 'path': 'notebooks/Analysis - OpenSearch.ipynb', 'repo_name': 'b-cube/Response-Identification-Info', 'size': '26334'}