Clever Geek Handbook

Node postgres requests for json

using node-pg I am trying to find the existence of a string inside a JSON object.

Example (part) of a line:

{ viq_id: '801583',
    title: 'Blank, security key, lock system, and production method',
    applicants: [ [Object], [Object] ],
    cpc: [ [Object], [Object] ],
    abstract: { value: [Object], language: 'en' } }

the abstract type JSONB. When requesting this

 var query = 'SELECT viq_id, title, applicants, cpc, abstract ->> "value"' +
    ' FROM epo_patents' +
    ' WHERE title ILIKE $1';

or for this

var query = 'SELECT viq_id, title, applicants, cpc, abstract' +
        ' FROM epo_patents' +
        ' WHERE title ILIKE $1 OR abstract ->> "value" = $1';

or for this

var query = 'SELECT viq_id, title, applicants, cpc, abstract' +
        ' FROM epo_patents' +
        ' WHERE abstract.value = $1';

the answer is "errorMissingColumn", or in the latter case errorMissingRTE

How to properly request JSON in node pg?

+3


source to share


1 answer


change var query = 'SELECT viq_id, title, applicants, cpc, abstract ->> "value"'

to var query = "SELECT viq_id, title, applicants, cpc, abstract ->> 'value'"

because double quotes used for db objects (table, column name, etc.) ... have a look at Postgres JSON Syntax



+2


source






More articles:


All Articles