example use sql

[sysadminmike]

Do a wiki/readme page of example sql queries eg:

SELECT DISTINCT doc->>'type' as doctype, count(doc->>'type') FROM mytable GROUP BY doctype ORDER BY doctype

And any things which might bite like the ORDER BY issue

[sysadminmike]

--- EXTRACT fieldname and fieldtype - where there is only one type of doc

• essentially same as looking at the doc but gets in table format:

-- show equivelent couchdb map / reduce

WITH flds AS ( SELECT DISTINCT jsonb_object_keys(doc) AS f, 'text' AS t from articlespg ), exampledoc AS ( SELECT doc FROM articlespg LIMIT 1),

fieldmeta AS (SELECT f as fname, json_typeof(to_json(doc)->f) as ftype FROM flds, exampledoc)

SELECT * FROM fieldmeta

-- i suspect there may be a simpler way todo this by grabbing one doc and use json functions to extract the info

[sysadminmike]

--- EXTRACT fieldname and fieldtype - where there is more than one type of doc

show equivelent couchdb map / reduce note this can take a long time to execute

WITH flds AS (SELECT DISTINCT doc->>'type' as doctype, jsonb_object_keys(doc) AS f, 'text' AS t FROM articlespg), exampledoc AS (SELECT DISTINCT(doc->>'type'), doc FROM articlespg), fieldmeta AS (SELECT doctype, f as fname, json_typeof(to_json(doc)->f) as ftype FROM flds, exampledoc ORDER BY doctype,f)

SELECT DISTINCT doctype, fname, (CASE ftype
WHEN 'boolean' THEN 'boolean' WHEN 'string' THEN 'text' WHEN 'number' THEN 'numeric' WHEN 'array' THEN 'text' --not sure maybe convert to postgres array or json? WHEN 'null' THEN 'text' WHEN 'object' THEN 'text' -- text/json ? WHEN '' THEN 'text' ELSE ftype || 'x' END) FROM fieldmeta WHERE ftype != '' ORDER BY doctype, fname