I'd like to obtain a list of patents (publication number, filing date, and etc.) that cite all US patents filed between 2003 and 2015. Not sure what went wrong, but my current codes (below) returned no results. Can anyone help me revise the codes please?
SELECT c.publication_number AS Pub, c.category AS Cat, c.filing_date AS Date, p.publication_number AS Citedby, p.filing_date AS Cited_Date
FROM `patents-public-data.patents.publications` AS p, UNNEST(citation) AS c
WHERE c.publication_number LIKE ('US%') AND (c.filing_date BETWEEN 20030101 AND 20041231);
Thank you so much for your kind help.
It's happening because citation.filing_date has 0 value in each row. Take a look on the JSON output file and a Date field:
[
{
"Citedby": "US-2014313399-A1",
"Cited_Date": "20140630",
"Pub": "US-8792047-B2",
"Cat": "PRS",
"Date": "0"
},
{
"Citedby": "US-2013120526-A1",
"Cited_Date": "20120731",
"Pub": "US-2011134210-A1",
"Cat": "PRS",
"Date": "0"
},
{
"Citedby": "US-2013242999-A1",
"Cited_Date": "20120530",
"Pub": "US-7616646-B1",
"Cat": "PRS",
"Date": "0"
},
You shouldn't use the (c.filing_date BETWEEN 20030101 AND 20041231) in WHERE clause:
SELECT p.publication_number AS Citedby, p.filing_date AS Cited_Date, c.publication_number AS Pub, c.category AS Cat, c.filing_date AS Date
FROM `patents-public-data.patents.publications` AS p, UNNEST(citation) AS c
WHERE c.publication_number LIKE ('US%');
It prints out list of patents. Or you can add (p.filing_date BETWEEN 20030101 AND 20151231), but remember that this the publications.filing_date not citation.filing_date.
I hope it helps.