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