How to query JSON in a Postgres table from an Express REST API-CodePudding

I am attempting to query a Postgres db through an Express REST API. One column (json_object_data) includes json, upon which I wish to query on the 'name' key.

It works fine if I enter the query directly into the db console. What's giving me an issue is passing a search term into an HTTP request to an Express route.

FYI, other requests to the Express app (GET, POST, PUT, and DELETE), that do not attempt to query the json column, work fine.

Node Express code:

app.get("/authorsname/:name", async(req, res) => {
    const { author_name } = req.params;
    console.log(req.params);
    try {
        const getAuthors = await pool.query("select json_object_data from \"jason-elwood/authors\".\"authors\" where lower(json_object_data ->> 'name') like lower($1%)", [author_name]);
        res.json(getAuthors);
        console.log(req.params);
    } catch (error) {
           console.error(error.message); 
    }
});

Sample json (from the json_object_data column):

{
    "name": "Jason Elwood", 
    "personal_name": "Jason Elwood", 
    "last_modified": {"type": "/type/datetime", "value": "2008-08-20T17:57:31.650087"}, 
    "key": "/authors/OL1000204A", 
    "birth_date": "1982", 
    "type": {"key": "/type/author"}, 
    "revision": 2
}

http GET request:

http://localhost:3000/authorsname/jason

Result:

server is listening on port 3000
{ name: 'jason' }
node:events:371
      throw er; // Unhandled 'error' event
      ^

error: unable to bind
    at Parser.parseErrorMessage (/Users/jasonelwood/Development/OpenbookAPIs/node_modules/pg-protocol/dist/parser.js:287:98)
    at Parser.handlePacket (/Users/jasonelwood/Development/OpenbookAPIs/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/Users/jasonelwood/Development/OpenbookAPIs/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/Users/jasonelwood/Development/OpenbookAPIs/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:394:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:199:23)
Emitted 'error' event on BoundPool instance at:
    at Client.idleListener (/Users/jasonelwood/Development/OpenbookAPIs/node_modules/pg-pool/index.js:57:10)
    at Client.emit (node:events:394:28)
    at Client._handleErrorEvent (/Users/jasonelwood/Development/OpenbookAPIs/node_modules/pg/lib/client.js:319:10)
    at Client._handleErrorMessage (/Users/jasonelwood/Development/OpenbookAPIs/node_modules/pg/lib/client.js:330:12)
    at Connection.emit (node:events:394:28)
    at /Users/jasonelwood/Development/OpenbookAPIs/node_modules/pg/lib/connection.js:114:12
    at Parser.parse (/Users/jasonelwood/Development/OpenbookAPIs/node_modules/pg-protocol/dist/parser.js:40:17)
    at Socket.<anonymous> (/Users/jasonelwood/Development/OpenbookAPIs/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:394:28)
    at addChunk (node:internal/streams/readable:315:12) {
  length: 92,
  severity: 'FATAL',
  code: 'XX000',
  detail: 'cannot get parse message ""',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'pool_proto_modules.c',
  line: '1414',
  routine: undefined,
  client: Client {
    _events: [Object: null prototype] { error: [Function (anonymous)] },
    _eventsCount: 1,
    _maxListeners: undefined,
    connectionParameters: ConnectionParameters {
      user: 'omitted',
      database: 'omitted',
      port: 5432,
      host: 'omitted',
      binary: false,
      options: undefined,
      ssl: false,
      client_encoding: '',
      replication: undefined,
      isDomainSocket: false,
      application_name: undefined,
      fallback_application_name: undefined,
      statement_timeout: false,
      idle_in_transaction_session_timeout: false,
      query_timeout: false,
      connect_timeout: 0
    },
    user: 'omitted',
    database: 'omitted',
    port: 5432,
    host: 'omitted',
    replication: undefined,
    _Promise: [Function: Promise],
    _types: TypeOverrides {
      _types: {
        getTypeParser: [Function: getTypeParser],
        setTypeParser: [Function: setTypeParser],
        arrayParser: { create: [Function: create] },
        builtins: {
          BOOL: 16,
          BYTEA: 17,
          CHAR: 18,
          INT8: 20,
          INT2: 21,
          INT4: 23,
          REGPROC: 24,
          TEXT: 25,
          OID: 26,
          TID: 27,
          XID: 28,
          CID: 29,
          JSON: 114,
          XML: 142,
          PG_NODE_TREE: 194,
          SMGR: 210,
          PATH: 602,
          POLYGON: 604,
          CIDR: 650,
          FLOAT4: 700,
          FLOAT8: 701,
          ABSTIME: 702,
          RELTIME: 703,
          TINTERVAL: 704,
          CIRCLE: 718,
          MACADDR8: 774,
          MONEY: 790,
          MACADDR: 829,
          INET: 869,
          ACLITEM: 1033,
          BPCHAR: 1042,
          VARCHAR: 1043,
          DATE: 1082,
          TIME: 1083,
          TIMESTAMP: 1114,
          TIMESTAMPTZ: 1184,
          INTERVAL: 1186,
          TIMETZ: 1266,
          BIT: 1560,
          VARBIT: 1562,
          NUMERIC: 1700,
          REFCURSOR: 1790,
          REGPROCEDURE: 2202,
          REGOPER: 2203,
          REGOPERATOR: 2204,
          REGCLASS: 2205,
          REGTYPE: 2206,
          UUID: 2950,
          TXID_SNAPSHOT: 2970,
          PG_LSN: 3220,
          PG_NDISTINCT: 3361,
          PG_DEPENDENCIES: 3402,
          TSVECTOR: 3614,
          TSQUERY: 3615,
          GTSVECTOR: 3642,
          REGCONFIG: 3734,
          REGDICTIONARY: 3769,
          JSONB: 3802,
          REGNAMESPACE: 4089,
          REGROLE: 4096
        }
      },
      text: {},
      binary: {}
    },
    _ending: true,
    _connecting: false,
    _connected: true,
    _connectionError: false,
    _queryable: false,
    connection: Connection {
      _events: [Object: null prototype] {
        newListener: [Function (anonymous)],
        connect: [Function (anonymous)],
        sslconnect: [Function (anonymous)],
        authenticationCleartextPassword: [Function: bound _handleAuthCleartextPassword],
        authenticationMD5Password: [Function: bound _handleAuthMD5Password],
        authenticationSASL: [Function: bound _handleAuthSASL],
        authenticationSASLContinue: [Function: bound _handleAuthSASLContinue],
        authenticationSASLFinal: [Function: bound _handleAuthSASLFinal],
        backendKeyData: [Function: bound _handleBackendKeyData],
        error: [Function: bound _handleErrorEvent],
        errorMessage: [Function: bound _handleErrorMessage],
        readyForQuery: [Function: bound _handleReadyForQuery],
        notice: [Function: bound _handleNotice],
        rowDescription: [Function: bound _handleRowDescription],
        dataRow: [Function: bound _handleDataRow],
        portalSuspended: [Function: bound _handlePortalSuspended],
        emptyQuery: [Function: bound _handleEmptyQuery],
        commandComplete: [Function: bound _handleCommandComplete],
        parseComplete: [Function: bound _handleParseComplete],
        copyInResponse: [Function: bound _handleCopyInResponse],
        copyData: [Function: bound _handleCopyData],
        notification: [Function: bound _handleNotification],
        end: [
          [Function: bound onceWrapper] {
            listener: [Function (anonymous)]
          },
          [Function: bound onceWrapper] {
            listener: [Function (anonymous)]
          },
          [Function: bound onceWrapper] {
            listener: [Function (anonymous)]
          }
        ]
      },
      _eventsCount: 23,
      _maxListeners: undefined,
      stream: <ref *1> Socket {
        connecting: false,
        _hadError: false,
        _parent: null,
        _host: 'db.bit.io',
        _readableState: ReadableState {
          objectMode: false,
          highWaterMark: 16384,
          buffer: BufferList { head: null, tail: null, length: 0 },
          length: 0,
          pipes: [],
          flowing: true,
          ended: false,
          endEmitted: false,
          reading: false,
          constructed: true,
          sync: false,
          needReadable: true,
          emittedReadable: false,
          readableListening: false,
          resumeScheduled: false,
          errorEmitted: false,
          emitClose: false,
          autoDestroy: true,
          destroyed: true,
          errored: null,
          closed: true,
          closeEmitted: false,
          defaultEncoding: 'utf8',
          awaitDrainWriters: null,
          multiAwaitDrain: false,
          readingMore: false,
          dataEmitted: true,
          decoder: null,
          encoding: null,
          [Symbol(kPaused)]: false
        },
        _events: [Object: null prototype] {
          end: [
            [Function: onReadableStreamEnd],
            [Function (anonymous)],
            [Function (anonymous)]
          ],
          error: [Function: reportStreamError],
          close: [Function (anonymous)],
          data: [Function (anonymous)]
        },
        _eventsCount: 4,
        _maxListeners: undefined,
        _writableState: <ref *2> WritableState {
          objectMode: false,
          highWaterMark: 16384,
          finalCalled: false,
          needDrain: false,
          ending: false,
          ended: false,
          finished: false,
          destroyed: true,
          decodeStrings: false,
          defaultEncoding: 'utf8',
          length: 0,
          writing: false,
          corked: 0,
          sync: false,
          bufferProcessing: false,
          onwrite: [Function: bound onwrite],
          writecb: null,
          writelen: 0,
          afterWriteTickInfo: {
            count: 1,
            cb: [Function (anonymous)],
            stream: [Circular *1],
            state: [Circular *2]
          },
          buffered: [],
          bufferedIndex: 0,
          allBuffers: true,
          allNoop: true,
          pendingcb: 1,
          constructed: true,
          prefinished: false,
          errorEmitted: false,
          emitClose: false,
          autoDestroy: true,
          errored: null,
          closed: true,
          closeEmitted: false,
          [Symbol(kOnFinished)]: []
        },
        allowHalfOpen: false,
        _sockname: null,
        _pendingData: null,
        _pendingEncoding: '',
        server: null,
        _server: null,
        [Symbol(async_id_symbol)]: 22,
        [Symbol(kHandle)]: null,
        [Symbol(kSetNoDelay)]: true,
        [Symbol(lastWriteQueueSize)]: 0,
        [Symbol(timeout)]: null,
        [Symbol(kBuffer)]: null,
        [Symbol(kBufferCb)]: null,
        [Symbol(kBufferGen)]: null,
        [Symbol(kCapture)]: false,
        [Symbol(kBytesRead)]: 784,
        [Symbol(kBytesWritten)]: 297
      },
      _keepAlive: false,
      _keepAliveInitialDelayMillis: 0,
      lastBuffer: false,
      parsedStatements: {},
      ssl: false,
      _ending: true,
      _emitMessage: false,
      _connecting: true,
      [Symbol(kCapture)]: false
    },
    queryQueue: [],
    binary: false,
    processID: -1631365830,
    secretKey: -1498717443,
    ssl: false,
    _connectionTimeoutMillis: 0,
    _connectionCallback: null,
    release: [Function (anonymous)],
    activeQuery: null,
    readyForQuery: true,
    hasExecuted: true,
    _poolUseCount: 1,
    [Symbol(kCapture)]: false
  }
}
[nodemon] app crashed - waiting for file changes before starting...

The same request directly in the db console (which produces a successful response):

select json_object_data from "jason-elwood/authors"."authors" where lower(json_object_data ->> 'name') like lower('jason%');

My guess is it's something to do with the structure of the query, particularly the way I'm including the request parameter in my request string. I've tried wrapping the entire request in single quotes and not escaping the table name like so:

const getAuthors = await pool.query('select json_object_data from "jason-elwood/authors"."authors" where lower(json_object_data ->> "name") like lower($1%)', [author_name]);

but it still produces an error.

Any help on this is greatly appreciated.

Thank you!

CodePudding user response：

At least % should be added to a passed value because you use query parameters:

const getAuthors = await pool
  .query("select json_object_data from \"jason-elwood/authors\".\"authors\" where json_object_data ->> 'name' ilike $1", [`${author_name}%`]);
res.json(getAuthors);

And you can use ilike instead of like to avoid using lower for both sides of the operation.

CodePudding user response：

So for anyone else coming to this in the future, the issue was how I was structuring the parameters when adding them to the query string. Because I'm using a LIKE operator, it needs to be formatted like this:

['%'   req.params.name   '%']

for reasons that are obvious to me now after banging my head on my desk for several hours. (Or what I had originally, which would be ['%' author_name '%']). So the final query looks like this:

const getAuthors = await pool.query("select json_object_data from \"jason-elwood/authors\".\"authors\" where lower(json_object_data ->> 'name') like lower($1)", ['%'   req.params.name   '%']);

I can breath again. :)